What is the syntax calling a stored procedure (let say named 'myproc') with OUTPUT variables (lets say the proc has an integer output variable named 'myvar') ?
I have a PROC that I want to modify via a variable I will pass in from ASP.Net.
Currently the PROC is as follows:
ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(30) AS Set @Search = '%'+ @Search + '%'
SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1, dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP, dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO FROM dbo.EDCclient INNER JOIN dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID WHERE (dbo.EDCclient.CLIENT like @Search) OR (dbo.EDCproperty.PROPERTY like @Search)
I now want to modify it so that the entire Where clause is created in ASp and passed in. So I think my new PROC should look as follows:
ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(200) AS
SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1, dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP, dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO FROM dbo.EDCclient INNER JOIN dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
@Search)
But I can't get SQL to accept the @Search in place of the Where... Any ideas?
I'm working on my first stored procedure, having just watched a video about how to use them. I have a working procedure that looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InMateSearch] @State nvarchar(255) AS BEGIN DECLARE @SqlString nvarchar(255) SELECT [FIRST NAME], [LAST NAME], [STATE] FROM members WHERE suspend=0 AND (State=@State OR Country=@State)ENDWhat I need to do is build the WHERE clause of the SELECT Statement based on the values of the parameters passed in. There will be a lot of them, but State will do for an example.I want to assign a variable, say SqlString = " suspend=0". Then I want to check the value of @State which is a parameter being passed in, then do something like this:If @State <> "" then SqlString = SqlString & " AND (State=@State OR Country=@State)" so that I'm only filtering by state if the state parameter isn't empty. Then I would like my SELECT statement to look like: SELECT [FIRST NAME], [LAST NAME], [STATE] FROM members WHERE (SqlString)I'm declaring SqlString as the video indicated, but in the video the variable is assigned a value from the database. I think I need a different kind of variable. If I try to do something like SqlString = "test" or @SqlString = "test" I get an error. Once I define or declare a variable I can use to build the string, how do I assign it to the WHERE clause?Diane
I've got a number of stored procedures that I have for reporting
All are of a similar starting format
For easier maintenance and to take away the need to change all of them if the methodology changes I want to split out shared code.
What I want to do is to take out the part that populates the @ID1 table into a separate stored proc which will be called from the report procs. The values from the shared proc will then be parsed back to the reporting proc.
I thought about using a function but I don't think it will be flexible enough as in certain cases I want to parse 2 or more IDs back into the final output.
I also don't want to make the code too complex so that it is relatively easy to read
CREATE PROC dbo.ReportM1 @ID INT AS DECLARE @ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID)) IF @ID = 0 INSERT INTO @ID1
[Code] ....
The first question I have is: can i do it with a table variable when going between procs or do i need to build a real table if i want it to maintain the logic in 1 place.
May be worth bearing in mind that the end user who will be executing the proc will only have read + execute stored proc access permissions so dropping, updating or creating real tables is not an option. #Temp tables are possible but since am using table variables throughout would prefer to stick with them.
I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?
I would like to know if there is a penalty for Varchar variables in stored procedures if I declare them Varchar(8000) instead of Varchar(1000). I have a lot of variables and sometimes the content will be more them 1000 characters. Is memory only allocated for the the actual contents or for the complete declared length?
I have been looking at the project Real reference implementation for doing auditing of data uploads. The tables and store procedures are in place using identical field and variable names. However, when running the package it does not update the relevant log entry with the end time and status.
I have performed the process manually running the stored procedures, providing the values directly and everything works fine.
I can only assume that the LogID variable is not being updated during the run and therefore the onEnd procedure cannot update the relevant log entry.
One side effect is that it does update the record when it€™s the first record entered into the table but not on any other inserts. Clearing the table each time is not an option.
This has become rather frustrating and would appreciate any assistance.
I have written a simple C# console application that create my own Stored Procedures the code is here ----------------------------------------------------------------------------
static void Main(string[] args) { SqlConnection cn; string strSql; string strConnection; SqlCommand cmd; strConnection="server=(local);database=Northwind;integrated security=true;"; strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount"; cn=new SqlConnection(strConnection); cn.Open(); cmd=new SqlCommand(strSql,cn); cmd.ExecuteNonQuery(); cn.Close(); Console.WriteLine("Procedure Created!"); }
------------------------------------------------------------------------------------ but it has some errors becuase of my strSql strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount"; I mean in creating the stored procedure if i delete the Output parameter from my stored procedure and my strSql would be somethimg like this strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID "; There will be no errors I use Visual Studio.NET 2003(full versoin)and MSDE(not Sql Server) Could someone help me solve this problem? Thanks and Regards.
I'm using the function below to output all of my stored procedures intoa text file. Fice, except that the output file does not reflect thenames of the stored procedures correctly if the name has been changed.For example, I create a stored procedure named: "sp123" and then renameit to "sp123DELETE" or "sp123 DELETE" or "sp123OLD" or "sp123 OLD" andwhat I end up with is four entries in the output file all having thestored procedure name "sp123."I stop the service and restart before outputting the file.Any help is appreciated.lqFunction ExportSP(myPath As String)Dim objSQLServer As New SQLDMO.SQLServerDim dbs As New SQLDMO.DatabaseDim sp As SQLDMO.StoredProcedureDim sptext As StringobjSQLServer.Connect <Servername>, <Username>, <Password>Set dbs = objSQLServer.Databases(<databasename>)Open myPath For Output As #1For Each sp In dbs.StoredProceduressptext = sp.TextPrint #1, sptext & _vbCrLf & vbCrLf & vbCrLf & _"*******" & _vbCrLf & vbCrLf & vbCrLfNextEnd Function
here's my code:my $sth = $dbhSQL->prepare('{call proc(?,?,?)}');$sth->bind_param(1,"asd");$sth->bind_param(2,"klm");$sth->bind_param_inout(3,$no_go, 1000);$sth->execute;print "no go = $no_go";while(my @row=$sth->fetchrow_array){print "@row";}$sth->finish;Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@iyt varchar(20),@no_go int OUTPUTASSET NOCOUNT ONDECLARE @id_err int,@ans_glue_err intBEGIN TRANSACTIONSELECT user_id FROM myTableWHERE user_id=@id AND iyt=@iytSET @id_err = @@ERRORIF @@ROWCOUNT <> 0BEGINSELECT date,date_mod FROM ans_glueWHERE user_id=@idSET @no_go = 0SET @ans_glue_err=@@ERRORENDELSEBEGINSET @no_go = 1ENDIF @id_err = 0 AND @ans_glue_err = 0BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONENDthe procedure runs perfectly in Cold Fusion, returning both recordsetsand output param, but in perl, it won't print the output param and Idon't know how to access the second recordsetHELP!
I've read that stored procedures should use output parameters instead ofrecordsets where possible for best efficiency. Unfortunately I need toquantify this with some hard data and I'm not sure which counters touse. Should I be looking at the SQL Server memory counters or somethingelse.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":
ALTER PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
And Table "titleauthor" is: au_id title_id au_ord royaltyper
172-32-1176 PS3333 1 100
213-46-8915 BU1032 2 40
213-46-8915 BU2075 1 100
238-95-7766 PC1035 1 100
267-41-2394 BU1111 2 40
267-41-2394 TC7777 2 30
274-80-9391 BU7832 1 100
409-56-7008 BU1032 1 60
427-17-2319 PC8888 1 50
472-27-2349 TC7777 3 30
486-29-1786 PC9999 1 100
486-29-1786 PS7777 1 100
648-92-1872 TC4203 1 100
672-71-3249 TC7777 1 40
712-45-1867 MC2222 1 100
722-51-5454 MC3021 1 75
724-80-9391 BU1111 1 60
724-80-9391 PS1372 2 25
756-30-7391 PS1372 1 75
807-91-6654 TC3218 1 100
846-92-7186 PC8888 2 50
899-46-2035 MC3021 2 25
899-46-2035 PS2091 2 50
998-72-3567 PS2091 1 50
998-72-3567 PS2106 1 100
NULL NULL NULL NULL //////////////////////////////////////////////////////////////////////////////////////////// I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Dim connection As SqlConnection = New
SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("byroyalty", connection)
command.CommandType = CommandType.StoredProcedure ................................................................... .................................................................. etc. //////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Can anyone tell me if it's possible in SQL Server 2000 to build dynamically a select statement and get some values to variables (simple not tables) from the select ?
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
Greetings All, I have a very large query that uses dynamic sql. Thesql is very large and it requires it to be broken into three componentsto avoid the nvarchar(4000) issue:SET @v_SqlString(N'')SET @v_SqlString2(N'')SET @v_SqlString3(N'')The sql is large and I don't have a problem with that so I will notpost it. However, in the last string the very last statement lookelike:SET @v_SqlString3(N'......SELECT @v_TotalRowsLoaded = @@ROWCOUNT, @v_ExitStat =@@ERROR')I want to catch this output and I am having problems, here is what myexecute looks like:EXEC('DECLARE @v_TotalRowsLoaded integerDECLARE @v_ExitStatus integerEXEC sp_executesql N''' + @v_SqlString + @v_SqlString2 +@v_SqlString3 + ''',N''@v_TotalRowsLoaded integer OUTPUT'',@v_TotalRowsLoaded OUTPUT,N''@v_ExitStatus integer OUTPUT'',@v_ExitStatus OUTPUT,N''@v_OLTPQualifiedPath nvarchar(1000)'',@v_OLTPQualifiedPath = ''' + @v_OLTPQualifiedPath + ''',N''@v_LoadTime datetime'', @v_LoadTime =''' + @v_LoadTime + '''')When I run it as is I am prompted with:Server: Msg 119, Level 15, State 1, Line 126Must pass parameter number 8 and subsequent parameters as '@name =value'. After the form '@name = value' has been used, all subsequentparameters must be passed in the form '@name = value'.You are required to pass five "5" arguments.Can anyone tell me why this is failing? What can I do?Any help would be greatly appreciated.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
The variable 1 is created as int32 and variable 2 is created as dattime.
When i execute the SQLtask, I get error:
[Execute SQL Task] Error: Executing the query "Exec mysp 'table1',OUTPUT,OUTPUT" failed with the following error: "Error converting data type nvarchar to int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What am i missing. I tried changing the data types adding the input variable also as a variable in the mapping. Nothing seems to work. Any ideas please?
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?