SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Parameters with Stored Procedures


Using Parameters with Stored Procedures

Author
Message
ALZDBA
ALZDBA
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53187 Visits: 9117

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
begin
set 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.PARAMETERS
where SPECIFIC_NAME = @ProcedureName
order by Ordinal_Position

print '---'

select ' sqlcmd.parameters("' + Parameter_Name + '").value = rij.Item("' + replace(Parameter_Name,'@','') + '")'
FROM INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @ProcedureName
order by Ordinal_Position

end



Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
BobAtDBS
BobAtDBS
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 372

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.




Student of SQL and Golf, Master of Neither
James Shipley
James Shipley
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 15

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


Kathi Kellenberger
Kathi Kellenberger
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4165 Visits: 347

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!



Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Mike C
Mike C
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10568 Visits: 1173
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.
David Bird
David Bird
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1846 Visits: 1300

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.



David Bird
Dinakar Nethi-176633
Dinakar Nethi-176633
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1845 Visits: 188
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 query
myCommand.ExecuteNonQuery()
conn.close()

******************
Dinakar Nethi
Life is short. Enjoy it.
******************
janusz pazgier
janusz pazgier
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 11

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


Kathi Kellenberger
Kathi Kellenberger
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4165 Visits: 347
Good advice!

Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
ALZDBA
ALZDBA
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53187 Visits: 9117

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

Done

VB.Net support from your SQLServer DBA



Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search