Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Using Parameters with Stored Procedures Expand / Collapse
Author
Message
Posted Friday, February 3, 2006 3:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 8:09 AM
Points: 6,735, Visits: 8,495

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #255550
Posted Friday, February 3, 2006 6:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 297, Visits: 335

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
Post #255591
Posted Friday, February 3, 2006 6:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 9:32 AM
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

Post #255593
Posted Friday, February 3, 2006 8:05 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:32 AM
Points: 769, Visits: 256

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
Microsoft
(Former SQL Server MVP)
Post #255652
Posted Friday, February 3, 2006 9:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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.
Post #255682
Posted Friday, February 3, 2006 11:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:17 AM
Points: 185, Visits: 1,024

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

My PC Quick Reference Guide
Post #255738
Posted Friday, February 3, 2006 4:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 5, 2013 12:21 PM
Points: 265, Visits: 164
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.
******************
Post #255801
Posted Friday, February 3, 2006 7:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 5:55 PM
Points: 5, 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

 

Post #255812
Posted Friday, February 3, 2006 7:20 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:32 AM
Points: 769, Visits: 256
Good advice!

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #255813
Posted Wednesday, February 8, 2006 3:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 8:09 AM
Points: 6,735, Visits: 8,495

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

Done

VB.Net support from your SQLServer DBA



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #256637
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse