Solved

Passing arguments to sql server stored procedure

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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

17 Experts available now in Live!

Get 1:1 Help Now