SQLServerCentral / Discuss Content Posted by Kathi Kellenberger / Article Discussions / Article Discussions by Author / Using Parameters with Stored Procedures / Latest Posts

RE: Using Parameters with Stored Procedures

praveen-435766 — Thu, 21 Aug 2008 22:47:52 GMT

The Logic given below has really finished my doubt which i was having in retrieving the output parameter in sql queries...I knew it how to fetch the output from front end and actually itried like this in sql queries but i forgot that at the time of retrieval we give output identifier that's why i was making mistake in the retrieval ....Thanx,Regards Praveen[font="Arial Black"][b][i]ALTER PROC usp_AddTwoIntegers @FirstNumber int = 5, @SecondNumber int, @Answer varchar(30) OUTPUT as Set @Answer = 'The answer is ' + convert(varchar,@FirstNumber + @SecondNumber)Declare @a int, @b int, @c varchar(30)Select @a = 1, @b = 3Exec usp_AddTwoIntegers @a, @b, @c OUTPUTSelect @c[/i][/b][/font]

RE: Using Parameters with Stored Procedures

Kathi Kellenberger — Fri, 29 Feb 2008 09:02:39 GMT

Great idea!

RE: Using Parameters with Stored Procedures

GSquared — Fri, 29 Feb 2008 09:01:30 GMT

Good article.One thing I like to include in parameter names is the direction. For example, "@Date_in" would be an input parameter, "@Date_out" would be an output parameter, "@Date_both" would be both.Variables declared in the body of the proc don't have either. (e.g.: "@Date")It makes it easier when I'm debugging or refactoring a proc, to be able to tell at a glance, whether I'm looking at an input parameter, an output parameter, or an internal variable.

RE: Using Parameters with Stored Procedures

Grant Fritchey — Fri, 29 Feb 2008 07:35:52 GMT

Nice article Kathi. Good job.

RE: Using Parameters with Stored Procedures

George H. — Fri, 29 Feb 2008 04:36:56 GMT

Kathi,Great article.One thing I wanted to add, which threw me for a loop a while back, is that if you use a return value when filling a datareader object that return value isn't available in vb.net code until after the datareader has iterated through all of its rows and closed. Which to me seems backwards. I always wanted to use the return value to make sure the SP didn't error-off and then run through my reader, but the return value isn't available until after I'd run through the reader. Weird.George H.

RE: Using Parameters with Stored Procedures

Kathi Kellenberger — Wed, 09 Jan 2008 07:50:05 GMT

Thanks, guys! This has been my most popular article. Glad that I have been able to help lots of people.

RE: Using Parameters with Stored Procedures

Jeff Moden — Tue, 08 Jan 2008 19:48:46 GMT

I know it's an older article, but I gotta say, nicely done, Kathi! Great examples for folks that don't know.

RE: Using Parameters with Stored Procedures

Bidyut Bhattacharjee — Tue, 08 Jan 2008 10:20:40 GMT

This is a very good article for starters like me.

RE: Using Parameters with Stored Procedures

Patrick-304111 — Wed, 24 May 2006 14:46:00 GMT

Thanks for the article

RE: Using Parameters with Stored Procedures

ALZDBA — Wed, 08 Feb 2006 03:15:00 GMT

... I love stuff like this. Maybe you should submit it to the script library! ...

Done

VB.Net support from your SQLServer DBA

RE: Using Parameters with Stored Procedures

Kathi Kellenberger — Fri, 03 Feb 2006 19:20:00 GMT

Good advice!

RE: Using Parameters with Stored Procedures

janusz pazgier — Fri, 03 Feb 2006 19:09:00 GMT

I think that two aspect of stored procedure are very important:

Optimization, after you run your stored procedure for the first time it is optimized and next time it works faster because server (MS SQL) store execution plan and use it at next attempt to run procedure. So if you have a very complicated sql query which access multiple tables when if you put your query in stored procedure it will return data faster.

Security if you get data from Web page and for example you create sql query based on them if you pass data from customer as parameter to stored procedure it will automatically prevent some code injection attack because SQL server will test data against expected format.

For this .Net code example I would even put this part with connection in try catch finally block or only try finally to be sure that connection is closed and disposed after we done with our code like:

Try

conn.open()........'your data processing

Finally

conn.close()conn.dispose

End Try

RE: Using Parameters with Stored Procedures

Dinakar Nethi-176633 — Fri, 03 Feb 2006 16:13:00 GMT

The article is basic and good. Regarding the VB.NET code posted, I would recommend opening the connection as late as possible and close as early as you can, than have the open statement at the beginning.and you also forgot to close the connection.so it would be:conn.Open()'execute the querymyCommand.ExecuteNonQuery()conn.close()

RE: Using Parameters with Stored Procedures

David Bird — Fri, 03 Feb 2006 11:36:00 GMT

The problem I find with developers creating stored procedures is the need to have database owner authority. I promote the stored procedures to production but they have full control in development.

I wish SQL Server had an authority just for Stored Procedure development.

RE: Using Parameters with Stored Procedures

Mike C — Fri, 03 Feb 2006 09:08:00 GMT

Or, if you wanted to do it the .NET way (as opposed to VB6 style), Fill a DataSet and access the two tables individually. That, or open a SqlDataReader and use the .NextResult method to hit up the next table.

RE: Using Parameters with Stored Procedures

Kathi Kellenberger — Fri, 03 Feb 2006 08:05:00 GMT

Since becoming a DBA 4 years ago, I haven't really kept up with the VB side of things. I have written only a handful of .Net apps with VB.Net 2003. I wonder if TableAdapter objects are new with .Net 2005, I'm not familiar with them.

Anyway, the real point of the article was supposed to be focused on using stored procedures with parameters. There are many ways of calling them from the program you are writing and many different languages, so I just showed a very simple example.

Alzdba -- thanks for sharing your code generating proc. I love stuff like this. Maybe you should submit it to the script library!

RE: Using Parameters with Stored Procedures

James Shipley — Fri, 03 Feb 2006 06:32:00 GMT

Thanks for the article. I just upgraded to VB.net 2005 from VB6 and started using TableAdapter objects to connect to SQL Server and stored procedures. It seems to work OK. Is this a good way to go? You don't seem to use these.

Thanks,

Jim Shipley

RE: Using Parameters with Stored Procedures

BobAtDBS — Fri, 03 Feb 2006 06:29:00 GMT

It's quite easy to build true, reusable command objects in VB. We do it this way (this is an update only command, so no return parameters:

Static cmdUpdate as adodb.command

If cmdUpdate is Nothing Then Set cmdUpdate = New ADODB.Command With cmdUpdate .ActiveConnection = GL_Conn .CommandText = "UpdateOrderItemOptions" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@ID", adGUID) .Parameters.Append .CreateParameter("@CUpdated", adBoolean) .Parameters.Append .CreateParameter("@VUpdated", adBoolean) .Parameters.Append .CreateParameter("@Checked", adBoolean) .Parameters.Append .CreateParameter("@UseDirect", adBoolean) .Parameters.Append .CreateParameter("@DirectCost", adCurrency) .Parameters.Append .CreateParameter("@VendorCost", adCurrency) End With End If cmdUpdate.Parameters("@ID").Value = !ID cmdUpdate.Parameters("@CUpdated").Value = !CUpdated cmdUpdate.Parameters("@VUpdated").Value = !VUpdated cmdUpdate.Parameters("@Checked").Value = !Checked cmdUpdate.Parameters("@UseDirect").Value = !UseDirect cmdUpdate.Parameters("@DirectCost").Value = !DirectCost cmdUpdate.Parameters("@VendorCost").Value = !VendorCost cmdUpdate.Execute , , adExecuteNoRecords

With this method, you only "create" the command once, then you fill it with variables as often as you like. I greatly prefer to create each parameter specifically, it's really not that much trouble.

RE: Using Parameters with Stored Procedures

ALZDBA — Fri, 03 Feb 2006 03:51:00 GMT

Once devs are pushed toward using parameters, they get confused as to why their sp is not reused and has many compilations when in use.The reason is most of the time, that the parameters are defined with a wrong datatypemapping in the application.

I use this proc the generate the .Net-code to define and use the parameters for a commandobject named sqlcmd. A simple copy/paste helps out in this case.

It is primitive, and it has some flaws, but it helps out in 90 % ...

CREATE proc spc_ALZ_CreateSqlParameter @ProcedureName sysname as beginset nocount on

Select cast(' wrkCmdParam = New SqlClient.SqlParameter' as varchar(4000))+ char(13) + ' With wrkCmdParam'+ char(13) + ' .ParameterName = "' + Parameter_Name + '"'+ char(13) + ' .SqlDbType = SqlDbType.' + Data_Type + char(13) + ' ''.DbType = DbType.String'+ char(13) + ' ' + isnull('.Size = ' + cast(Character_Maximum_Length as varchar(15)) , '''.Size = ') + ''+ char(13) + ' .Direction = ParameterDirection.' + case Parameter_Mode when 'IN' then 'Input' else '???' end+ char(13) + ' End With'+ char(13) + ' sqlcmd.Parameters.Add(wrkCmdParam) '+ char(13) + ' '

--select *FROM INFORMATION_SCHEMA.PARAMETERSwhere SPECIFIC_NAME = @ProcedureNameorder by Ordinal_Position

print '---'

select ' sqlcmd.parameters("' + Parameter_Name + '").value = rij.Item("' + replace(Parameter_Name,'@','') + '")'FROM INFORMATION_SCHEMA.PARAMETERSwhere SPECIFIC_NAME = @ProcedureNameorder by Ordinal_Position

end

RE: Using Parameters with Stored Procedures

Kathi Kellenberger — Wed, 10 Aug 2005 20:17:00 GMT

Thank you for all of your comments. With third-party apps, you usually don't have a choice about whether or not stored procs are used. My real intention for the article wasn't really to start a discussion about the pros and cons of their use, just to show how to use them. Anyway, my opinion is probably pretty obvious from the article.

Several of you made a good point about the return value. I was just really trying to show how to use it, but looking back I see that my example was poor. I'm not sure if it is possible to edit an article after it has been already posted. I'll shoot Steve an email about it and see what he says.

Thanks again for reading the article and your constructive comments. I just started writing this past winter and know I have a lot to learn!

Kathi

RE: Using Parameters with Stored Procedures

Mike C — Tue, 09 Aug 2005 09:16:00 GMT

Nice article, as always. I would just point out that when you use a default value for an input parameter, you can actually leave the parameter out of the call if you want.

RE: Using Parameters with Stored Procedures

Jim Collet — Mon, 08 Aug 2005 09:35:00 GMT

good article but i think there is a typo in one example (it returns @sum , but should return @answer) i rarely use return except for maybe a 0 or 1: i would select instead.Dewes, I don’t think anybody addressed your question and I didn’t see anything about 'post your question in the XXXXX forum', so here goes (simply).SP_spaceused is a system stored procedure that returns 2 recordsets: you can access these as recordsets in VB Script just like you would any other recordset, but there are 2 so you will need to access the 2nd one with rs.nextrecordset:got this from VB Help: paste it in a vbs file, point it to a server and db by changing the connection dtring and save and run:set strCnn = createobject("adodb.connection")strCnn.connectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XX_DBNAME_XX;Data Source=XX_ServerName_XX"strCnn.openSet rstCompound = createobject("adodb.Recordset") rstCompound.Open "exec SP_spaceused", strCnn, , , adCmdText' Display results from each SELECT statement.intcount = 1 Do Until rstCompound Is Nothing msgbox "Contents of recordset #" & intCount Do While Not rstCompound.EOF msgbox rstCompound.Fields(0) & ", " & rstCompound.Fields(1) rstCompound.MoveNext Loop Set rstCompound = rstCompound.NextRecordset intCount = intCount + 1 Loop--you are going to have to modify the code to get the data how you want it.another option might even be easier:point to master in QA and paste 'sp_helptext SP_spaceused'. copy it then modify it to return 5 or 6 output params as describe in the article. the 1st option you can do without modifying the database. you can create your own proc with the new sql and decide where to create and how to name it

RE: Using Parameters with Stored Procedures

Brett Slaski — Mon, 08 Aug 2005 08:52:00 GMT

Plus if your stored procedures are ANSI compliant then your application will port to other databases quicker.I always wondered why anyone would want to keep the integrety of a database in software code. Especially in this day and age of multiple apps using the same data. 4GL apps did this and look how locked in you are to them. To write anything out side of them is a fustrating adventure. With environments like C#, which allow oop, general methods can be written which can call any stored prodecudure by just passing a parameter list. Why give up perfomance and integrety for code control?Brett

RE: Using Parameters with Stored Procedures

Lee Dise — Mon, 08 Aug 2005 08:38:00 GMT

Maybe we've been dealing with the wrong vendors.

RE: Using Parameters with Stored Procedures

noeld — Mon, 08 Aug 2005 08:34:00 GMT

Contrary to what you said I have worked with companies in the past that learned the HARD way that when the amount of non trivial queries or users become part on a DB systems if you don't use Stored procedures your system will CRAWL. When performance is necesary and the amount of data is "non trivial" stored procedures are the only way

RE: Using Parameters with Stored Procedures

Lee Dise — Mon, 08 Aug 2005 08:18:00 GMT

From what I've been seeing in recent years, the industry trend is moving away from using stored procedures. This is not necessarily due to simple ignorance on how stored procedures work, but rather due to conditions in the business environment.

Software vendors have an incentive to appeal to as many prospective customers as they can, and many of them have made the decision to try to support multiple database platforms. One of our vendors, a well-known one, supports Oracle, SQL Server and (if I'm not mistaken) Informix, and they're hardly alone. This means they have an incentive to write their SQL as generically as they possibly can so that it will run effectively on whatever database platform the customer prefers. This means, among other things, eschewing stored procedures -- Oracle PL/SQL procedures are simply not interchangeble with T-SQL procedures, architecturally. For one thing, Oracle procedures don't return results sets. (At least they didn't do so four years ago, my last direct exposure to Oracle.)

Our experience with vendors that support multiple databases is that they like to stick to straight SQL, and to keep that very simple -- preferring to do any extensive data manipulation in another tier.

RE: Using Parameters with Stored Procedures

Dewes van Lohuizen — Mon, 08 Aug 2005 02:45:00 GMT

It sounds clear to me.

But... I am looking for how to get the output from SP_spaceused in variables. Now I can use the output in a text file and I have seen on the internet how to catch it in a temptable. But what I really want is to get the output in variables with my own defined length. Is there anyone who can explain that to me?

Regards,

Dewes van Lohuizen

RE: Using Parameters with Stored Procedures

Andrew-152680 — Sun, 07 Aug 2005 22:50:00 GMT

Nice, except starting off with an example using the RETURN to return the answer - something I've seen too many programmers use because they don't understand its reason for existence

Maybe you can add a paragraph on what it is (INT only), why it exists (return error / success information, similar to C - not VB where you can return any data type) and how to catch it in ADO (and maybe what to do with it!)?

And maybe link to some nice articles about error handling (why and best practice - or worst :-) )...

Using Parameters with Stored Procedures

Kathi Kellenberger — Mon, 25 Jul 2005 18:53:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/usingparameterswithstoredprocedures.asp