Passing arguments to sql server stored procedure

how can i pass the parameter as table name to sql server ?
SachinChughAsked:
Who is Participating?
 
kingwr12Connect With a Mentor Commented:
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
 
dante469Commented:
lets say the webform has a textbox called tbTableName....

in codebehind....

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

Have Fun,
dante
0
 
gillitCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.