Accessing A 'View' From Within A Stored Procedure

Jun 7, 2004

Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.

The syntax I'm using is as follows :

SELECT TOP 1 @MyJobN = IJobN FROM MyView

I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.

SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView

But alas to no avail!


Any offers kind people??

View 12 Replies


ADVERTISEMENT

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Accessing Stored Procedure In ASP.Net - What Is Wrong?

Jul 25, 2006

I have the following stored proceduredrop procedure ce_selectCity;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================create PROCEDURE ce_selectCity    @recordCount int output    -- Add the parameters for the stored procedure here        --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>AS        declare @errNo         int                -- SET NOCOUNT ON added to prevent extra result sets from        -- interfering with SELECT statements.        SET NOCOUNT ON;                    -- Insert statements for procedure here        select ciId,name from ce_city order by name                select @recordCount = @@ROWCOUNT        select @errNo = @@ERROR        if @errNo <> 0 GOTO HANDLE_ERROR        return @errNoHANDLE_ERROR:    Rollback transaction    return @errNoGoand i was just testing it likeProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        db.connect()                Dim reader As SqlDataReader        Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)        Dim recordCountParam As New SqlParameter("@recordCount", SqlDbType.Int)        Dim errNoParam As New SqlParameter("@errNo", SqlDbType.Int)        recordCountParam.Direction = ParameterDirection.Output        errNoParam.Direction = ParameterDirection.ReturnValue        sqlCommand.Parameters.Add(recordCountParam)        sqlCommand.Parameters.Add(errNoParam)        reader = db.runStoredProcedureGetReader(sqlCommand)        If (db.isError = False And reader.HasRows) Then            Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")            While (reader.Read())                Response.Write(reader("ciId") & "::" & reader("name") & "<br />")            End While        End If        db.close()    End SubIt returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).BUT, its returning 0.What am i doing wrong??EDIT:Oh, and this is the function i use to execute stored procedure and get the readerPublic Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader            sqlCommand.CommandType = CommandType.StoredProcedure            Return sqlCommand.ExecuteReader        End Function

View 5 Replies View Related

Accessing Stored Procedure Parameters Through XSD

Dec 9, 2006

Hi All,
 I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD.
Thanks,
 
Long Live Microsoft ;)
 

View 4 Replies View Related

Accessing Web.config From Stored Procedure

Feb 27, 2007

I want to access a key from appSettings  section of web.config. 
I have the number of days allowed for a user to activate his/her account  as a key in appSettings.
I have a maintenance procedure to delete all accounts that are not activated before that many days.
In this context, i have to access web.config from stored procedure.  The procedure will be scheduled as a JOB in sql server.
Thanks.
 

View 3 Replies View Related

Error In Accessing Stored Procedure

Oct 10, 2006

i'm trying to insert into db sql 2000 through a stored procedure .. i got this error " Procedure or function newuser has too many arguments specified "

this is the procedure :



ALTER PROCEDURE newuser

(@username_1 [nvarchar](80),

@email_2 [nvarchar](50),

@password_3 [nvarchar](256),

@Country_ID_4 [int],

@city_id_5 [nvarchar](10),

@gender_6 [nvarchar](50),

@age_7 [int],

@fname_8 [nvarchar](50),

@lname_9 [nvarchar](50),

@birthdate_10 [datetime])

AS INSERT INTO [Brazers].[dbo].[users]

( [username],

[email],

[password],

[Country.ID],

[city.id],

[gender],

[age],

[fname],

[lname],

[birthdate])


VALUES

( @username_1,

@email_2,

@password_3,

@Country_ID_4,

@city_id_5,

@gender_6,

@age_7,

@fname_8,

@lname_9,

@birthdate_10)







& that 's the code in asp page :



Dim param As New SqlClient.SqlParameter

SqlConnection1.Open()

param.ParameterName = "@username_1"

param.Value = TextBox1.Text

param.Direction = ParameterDirection.Input

SqlCommand1.Parameters.Add(param)

SqlCommand1.ExecuteNonQuery()







plz .. waiting any solve for this problem immediately

View 2 Replies View Related

Accessing Another Database In A Stored Procedure

Aug 10, 2006

hi

i have stored procedure and i need to access another database in my stored procedure

I'm going to query a table which is located in another database



as you know it is impossible to use the USE database keyword in stored procedures

so what should I do?

thanks.

View 2 Replies View Related

Accessing SELECT Results Within A Stored Procedure

May 18, 2004

Hi,
Can anyone tell me how to access the results from a select statement within the stored procedure?

This is to implement auditting functionality, before updating a record i want select the original record and compare the contents of each field with the varibles that are passed in, if the fields contents has changed i want to write an audit entry.

So i'd like to store the results of the select statement in a variable and access it like a dataset. e.g

declare selectResult

set selectResult = Select field1,field2,field3 from table1

if selectResult.field1 <> @field1
begin
exec writeAudit @var1,@var2,var3
end


Many thanks.

View 4 Replies View Related

Error While Accessing COM Component From .NET Stored Procedure..

May 7, 2006

I have a COM object that is written using Visual Basic 6. This is referenced in a .NET Stored Procedure. But when I execute the stored procedure I get an error:

Msg 6522, Level 16, State 1, Procedure prCalculator_ExecCalc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'prCalculator_ExecCalc':

System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {844E8165-ABC1-432B-9490-51B1A6D91E71} failed due to the following error: 80040154.

System.Runtime.InteropServices.COMException:



Here is what I do:

1. Compile the VB DLL.

2. Convert into a .NET assembly by importing to a Visual Studio 2005 project. Sign the interop assembly.

3. Register it as an assembly in SQL 2005 server.

4. Create a .NET stored procedure and reference the above assembly. Compile this assembly again in SQL 2005 and create a stored procedure using the assembly. This assembly is also signed.

Please Note:

1. All the assemblies are registered with UNSAFE permissions.

2. They are compiled with COM Visible flag in Visual Studio 2005.

3. This works perfectly on my local SQL Express where I have Visual Basic/VisualStudio 2005 installed.

4. I get this error, when trying on Test Server. I tried to install the VB Runtime on this machine and still does not work.

So, what am I missing? Thanks for your help.





















.

View 4 Replies View Related

Problem With Accessing Stored Procedure In The Report

Mar 14, 2007

Hi,

I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)

Could not find stored procedure 'stpBranch'.

But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:





An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:





[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102
Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64


I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.

I am not sure why I am getting this error and I greatly appreciate any help.

Thanks



View 2 Replies View Related

Accessing Multiple Databases From Stored Procedure In SQL 2000

May 16, 2006

Hi ,

I have around 5 databases with same structure used to store data for different locations and services.

I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.

My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.

I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..

--------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE TestDB_access(@dbname varchar(100))
AS

BEGIN

DECLARE @localDB varchar(100)

Set @LocalDB=@dbname

if @LocalDB='XYZ'

EXEC ('USE XYZ ')
elseif @LocalDB='ABC'

EXEC ('USE ABC')


Select * from Mytable

END

---------------------------------------------------------------------------------------------------------------

When I run this from my database , it gives me an error "unable to find table "mytable".

What is the best way to make my queries work with all the databases.

Thanks in advance

Venu Yankarla

View 4 Replies View Related

Trouble Accessing SQL Server 2005 Stored Procedure Parameters

Jun 14, 2007

I created a stored procedure (see snippet below) and the owner is "dbo".
I created a data connection (slcbathena.SLCPrint.AdamsK) using Windows authentication.
I added a new datasource to my application in VS 2005 which created a dataset (slcprintDataSet.xsd).
I opened up the dataset in Designer so I could get to the table adapter.
I selected the table adapter and went to the properties panel.
I changed the DeleteCommand as follows: CommandType = StoredProcedure; CommandText = usp_OpConDeleteDirectory. When I clicked on the Parameters Collection to pull up the Parameters Collection Editor, there was no parameters listed to edit even though there is one defined in the stored procedure. Why?

If I create the stored procedure as "AdamsK.usp_OpConDeleteDirectory", the parameters show up correctly in the Parameters Collection Editor. Is there a relationship between the owner name of the stored procedure and the data connection name? If so, how can I create a data connection that uses "dbo" instead of "AdamsK" so I can use the stored procedure under owner "dbo"?



Any help will be greatly appreciated!



Code SnippetCREATE PROCEDURE dbo.usp_OpConDeleteDirectory
(
@DirectoryID int
)
AS
SET NOCOUNT ON
DELETE FROM OpConDirectories WHERE (DirectoryID = @DirectoryID)

View 1 Replies View Related

Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output &&amp; 2 Input Parameters In Param. Coll.?

Feb 23, 2008

Hi all,

In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":

CREATE PROC [dbo].[spInvTotal3]

@InvTotal money OUTPUT,

@DateVar smalldatetime = NULL,

@VendorVar varchar(40) = '%'



This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub printMyInvTotal()

Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"

Dim conn As SqlConnection = New SqlConnection(connectionString)

Try

conn.Open()

Dim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[dbo].[spInvTotal3]"

Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)

param.Direction = ParameterDirection.Output

cmd.Parameters.Add(param)

cmd.ExecuteNonQuery()

'Print out the InvTotal in TextBox1

TextBox1.Text = param.Value

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

conn.Close()

End Try

End Sub

End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?

Please help and advise.

Thanks in advance,
Scott Chang



View 7 Replies View Related

Should I Use A View Or A Stored Procedure

Apr 5, 2007

I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views.  Does anyone know why someone would do this?  I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?  

View 1 Replies View Related

View Vs. Stored Procedure

Jul 7, 2000

Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.

View 3 Replies View Related

Stored Procedure Vs View

Jan 23, 2006

I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?

View 3 Replies View Related

Stored Procedure Or View?

Mar 14, 2007

Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...

with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?

CallID DateStopTimeStopCallStatus
002161772006-01-2005:39:24Open
002161772006-01-2005:39:27Open
002161772006-01-2005:40:13Open
002161772006-01-2005:40:24Pending
002161772006-02-0716:05:47Pending
002161772006-02-2117:26:22Pending
002161772006-02-2117:29:06Pending
002161772006-02-2117:29:08Open
002161772006-03-0316:35:10Open
002161772006-04-0515:12:26Open
002161772006-04-0515:17:09Open
002161772006-04-1414:37:49Open
002161772006-04-1414:37:54Awaiting
002161772006-04-1911:20:30Awaiting
002161772006-04-1912:12:34Awaiting
002161772006-04-1912:12:37Awaiting
002161772006-04-1912:12:58Awaiting
002161772006-04-1912:13:00Closed[/b]

View 20 Replies View Related

Stored Procedure From The View

Sep 25, 2007

How can I create a stored procedure that combines the results from three views, and puts them in a temp table?

View 20 Replies View Related

Using A Stored Procedure In A View

Jan 1, 2008

Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.

If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.

Thanks.

View 1 Replies View Related

Using A Stored Procedure In A View

Jan 1, 2008

Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.

If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.

Thanks.

View 1 Replies View Related

Help Coverting A Stored Procedure To A View

Aug 13, 2007

Can someone help me convert this stored procedure to a view? It is using two UDFs.
I appreciate this very much!@Start datetime,
@End datetime

AS

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as Description
FROM

tblClients C INNER JOIN
tblPMTs P

ON C.Client_ID = P.Client_ID INNER JOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_ID INNER JOIN
tblClientAddresses A

ON C.Client_ID = A.Client_ID LEFT OUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDate BETWEEN @Start AND @End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

ORDER

BY pmtLevel
RETURN 

View 3 Replies View Related

Using Stored Procedure In View As A Table ?!

Aug 28, 2007

Hi guys
I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs  must be ordered  in certain  way , for example "1st managers then engineers  … workers  … " so In the table that   job titles are defined  there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order .
The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that.
Any Idea?
 

View 8 Replies View Related

Updating A View Through A Stored Procedure.

Oct 14, 2007

Hi i have a page in which a user fills out info on a page, the problem i am getting is that when the save button is clicked all text box values apart from one are saving to the database this field is the "constructor_ID" field. The save button performs a stored procedure, however there is a view which is doing something as well, would it be possible to write a stored procedure which would update the view at the same time?
CREATE PROCEDURE sp_SurveyMainDetails_Update
@Constructor_ID  int,@SurveyorName_ID int,@Survey_Date char(10),@Survey_Time char (10),@AbortiveCall bit,@Notes  text,@Survey_ID int,@User_ID int,@Tstamp timestamp out AS
 
DECLARE @CHANGED_Tstamp timestampDECLARE @ActionDone char(6)SET @ActionDone = 'Insert'
SET @CHANGED_Tstamp = (SELECT Tstamp FROM tblSurvey WHERE Survey_ID = @Survey_ID)IF @Tstamp <> @CHANGED_Tstamp --AND @@ROWCOUNT =0 BEGIN  SET @Tstamp =  @CHANGED_Tstamp  RAISERROR('This survey has already been updated since you opened this record',16,1)  RETURN 14 ENDELSE
   BEGIN
SELECT * FROM tblSurvey WHERE  Constructor_ID   = @Constructor_ID   AND  --Contractor_ID  = @Contractor_ID  AND  Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) AND  IsAbortiveCall = @AbortiveCall     IF @@ROWCOUNT>0                          SET @ActionDone = 'Update'
UPDATE tblSurvey SET    Constructor_ID   = @Constructor_ID   ,  SurveyorName_ID   = @SurveyorName_ID ,     Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) ,  IsAbortiveCall = @AbortiveCall ,  Note  = @Notes               WHERE Survey_ID = @Survey_ID AND Tstamp = @Tstamp IF @@error = 0 begin                        exec dhoc_ChangeLog_Insert    'tblSurvey',  @Survey_ID,  @User_ID,  @ActionDone,  'Main Details',  @Survey_ID
    end else BEGIN  RAISERROR ('The request has not been proessed, it might have been modifieid since you last opened it, please try again',16,1)  RETURN 10   END SELECT * FROM tblSurvey WHERE Survey_ID=@Survey_ID     
END
--Make sure this has saved, if not return 10 as this is unexpected error
--SELECT * FROM tblSurvey
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
 This is the view;
CREATE VIEW dbo.vw_Property_FetchASSELECT     dbo.tblPropertyPeriod.Property_Period, dbo.tblPropertyType.Property_Type, dbo.tblPropertyYear.Property_Year, dbo.tblProperty.Add1,                       dbo.tblProperty.Add2, dbo.tblProperty.Add3, dbo.tblProperty.Town, dbo.tblProperty.PostCode, dbo.tblProperty.Block_Code, dbo.tblProperty.Estate_Code,                       dbo.tblProperty.UPRN, dbo.tblProperty.Tstamp, dbo.tblProperty.Property_ID, dbo.tblProperty.PropertyStatus_ID, dbo.tblProperty.PropertyType_ID,                       dbo.tblProperty.Correspondence_Add4, dbo.tblProperty.Correspondence_Add3, dbo.tblProperty.Correspondence_Add2,                       dbo.tblProperty.Correspondence_Add1, dbo.tblProperty.Correspondence_Phone, dbo.tblProperty.Correspondence_Name,                       dbo.tblPropertyStatus.Property_Status, dbo.tblProperty.Floor_Num, dbo.tblProperty.Num_Beds, dbo.vw_LastSurveyDate.Last_Survey_Date,                       dbo.tblProperty_Year_Period.Constructor_ID, dbo.tblProperty_Year_Period.PropertyPeriod_ID, dbo.tblProperty_Year_Period.PropertyYear_ID,                       LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add1, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add2, '')))                       + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add3, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.PostCode, ''))) AS Address,                       dbo.tblProperty.TenureFROM         dbo.tblPropertyType RIGHT OUTER JOIN                      dbo.tblProperty LEFT OUTER JOIN                      dbo.tblProperty_Year_Period ON dbo.tblProperty.Property_ID = dbo.tblProperty_Year_Period.Property_ID LEFT OUTER JOIN                      dbo.vw_LastSurveyDate ON dbo.tblProperty.Property_ID = dbo.vw_LastSurveyDate.Property_ID LEFT OUTER JOIN                      dbo.tblPropertyStatus ON dbo.tblProperty.Status_ID = dbo.tblPropertyStatus.PropertyStatus_ID ON                       dbo.tblPropertyType.PropertyType_ID = dbo.tblProperty.PropertyType_ID LEFT OUTER JOIN                      dbo.tblPropertyPeriod ON dbo.tblProperty.PropertyPeriod_ID = dbo.tblPropertyPeriod.PropertyPeriod_ID LEFT OUTER JOIN                      dbo.tblPropertyYear ON dbo.tblProperty.PropertyYear_ID = dbo.tblPropertyYear.PropertyYear_ID
   
 

View 1 Replies View Related

Is It Possible To Create A View From A Stored Procedure

Jan 3, 2008

Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.

View 9 Replies View Related

When To Use ? Stored Procedure V/s View In SQL Server

Dec 6, 2005

hi,
Can someone tell me when to use SQL Server View as oppose to Stored Porcedure?
Currently we do everything with SQL Server stored procedure. I mean, even if we have to display some report, we use Stored Procedure.
In what situations and senarios views are better and one should consider them over Stored Procedure?

View 13 Replies View Related

View Creation Using Stored Procedure

Apr 21, 2001

Hi

I need to create a view using a stored procedure .

The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration.
After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.

I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another .
I am thinking to create a view though a stored procedure .
You can suggest me alternate ways to do same .


Sujit

View 1 Replies View Related

Stored Procedure - Can I Use 4-part Name With A &#34;VIEW&#34;?

Aug 3, 2001

Joe, thank you for the answer to my post.

Can I also use a

SELECT field1 FROM DBName.dbo.TableName with a "VIEW" in this other database (that's on the same server)?

Also, in my sp, I have the following:

SELECT DISTINCT Store.[DemoID#], Progstats.ProgramName, Progstats.[Program#], ZCHAIN.STR_NAME, ZCHAIN.[STR#], ZCHAIN.ADDRESS,
ZCHAIN.City, ZCHAIN.ST, ZCHAIN.ZIP, ZCHAIN.[PHONE#], Store.D1, Store.Status, Store.AgencyCompleted,
Store.Reason, Store.LeadName, Store.DemonstratorName, Store.UpdatedOnline
FROM (Store INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]) INNER JOIN ZCHAIN ON Store.[TD#] = ZCHAIN.[ID#]
WHERE (((Store.[DemoID#])=@DemoID)) AND Progstats.Status=1;
GO

ZCHAIN has now become this "VIEWZCHAIN" in this other database. So, could I simply relace "ZCHAIN" with "DB2.dbo.VIEWZCHAIN.STR_NAME" which is actually now a 4-part name?



Thanks,
John Wilson

View 1 Replies View Related

Call Stored Procedure From View

Sep 7, 2006

Hello all,
does anyone know if it's possible to call a stored procedure from a view.

Thnx,
Patrick

View 9 Replies View Related

Creating A View In A Stored Procedure

Apr 2, 2004

Hi Everyone

Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.

CREATE PROC upProcName AS

DECLARE @Variable varchar(50)

CREATE VIEW vwName AS

SELECT DISTINCT Table1.*, Table2.*
FROM dbo.Table1
INNER JOIN dbo.Table2 AS BUG
ON Table1.Col1 = Table2.Col1
WHERE LI.accname = @Variable

GO

Any Thoughts ideas would be great

Cheers

View 7 Replies View Related

Need Help To Create A View/Stored Procedure

Jun 24, 2008

Experts

I am trying to create a view or Stored Procedure between different table

Table1 consist of the follwing Fields:

Ref_No: String hold the reference number, Unique
Details: String

Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique

What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers

Any advice?

View 4 Replies View Related

Create View Within A Stored Procedure

May 21, 2007

hi,

Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go

go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?

thank you

View 20 Replies View Related

Creating A View From A Stored Procedure

Sep 19, 2007

HelloNewbie here.Is there a way of creating a VIEW...using a stored procedure. I ambasically trying to create a view to return some data that I amgetting using a stored procedure.I have created the procedure and when I execute this its working ok.The stored procedure uses a datefrom and dateTo which I have set up bytweaking the getdate() and getdate()-2.In other words can you create a view like thisCREATE VIEW view_testASexec proc_testGOAny help will be greatly appreciated.Remmy

View 1 Replies View Related

Cretae A View In One DB With A Stored Procedure From Another DB

Jul 20, 2005

Hi,Ik created an application with visuals basic.NET. This has aconnection string to one database, let's say 'A'. In this database astored procedure is called which should execute a string (which ispassed by the) VB tool. This string is a CREATE VIEW statement en thisshould be executed in another database let's say 'B'.I tried this in Transact - SQLEXEC('USE B;' + Query)An error occurs : CREATE VIEW should be the first in a batchedstatement.Could anyone help me with this one?Greetz,Hennie

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved