Solved

Passing arguments to sql server stored procedure

Posted on 2004-04-12
5
1,599 Views
Last Modified: 2012-06-21
how can i pass the parameter as table name to sql server ?
0
Comment
Question by:SachinChugh
5 Comments
 
LVL 7

Expert Comment

by:dante469
ID: 10807766
lets say the webform has a textbox called tbTableName....

in codebehind....

Dim sSql as string = "select * from " & tbTableName

Have Fun,
dante
0
 
LVL 3

Expert Comment

by:gillit
ID: 10807916
Didn't quite understand your question... I interpreted your question to be asking how to pass a parameter to execute a stored procedure.

string strSql = "EXECUTE spTestProcName '" + parameter1 + "'";

First you may want to make sure that your stored procedure works with the query analyzer:
EXECUTE spTestProcName 'tblTestTableName'

parameter1 is the string of your table name.
spTestProcName is the name of your stored procedure.

Hope this helps.
0
 

Accepted Solution

by:
kingwr12 earned 125 total points
ID: 10808373
WARNING: Using any of the above solutions subjects your database to SQL injection hacks!

If you simply want to pass a parameter to a stored procedure, I would use the parameters collection of the SqlCommand property instead, e.g. if you want to call a stored procedure called "GetData" with a parameter named "Parm1":

Dim cmd As New SqlClient.SqlCommand("GetData", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Parm1", LCase(sParmValue))
Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader

Note that this also works if you still want to use dynamic queries but want to protect against SQL injection attacks:

Dim cmd As New SqlClient.SqlCommand("SELECT * FROM Table WHERE UserID = @Parm1", conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@Parm1", LCase(sParmValue))
Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader

If you wish to pass a table name for a SELECT query, you will have to use dynamic SQL (either in your code or in the stored procedure), so run the input table name through some checks to ensure there is not injected SQL hacks.

WRK
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question