Solved

Passing arguments to sql server stored procedure

Posted on 2004-04-12
5
1,596 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now