Solved

Passing arguments to sql server stored procedure

Posted on 2004-04-12
5
1,602 Views
Last Modified: 2012-06-21
how can i pass the parameter as table name to sql server ?
0
Comment
Question by:SachinChugh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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