Creating a stored procedure in SQL

Tags:
Parameters
SQL
Stored Procedures
I need to create a stored procedure in SQL where the clause WHERE is a parameter. Is this possible? If yes, how?

Answer Wiki

Thanks. We'll let you know when a new response is added.

This can be done using dynamic SQL.

<pre>CREATE PROCEDURE YourProcedure
@WhereColoum sysname,
@WhereValue varchar(20)
AS
DECLARE @SQL nvarchar(4000)
SET @SQL = ‘SELECT *
FROM YourTable
WHERE ‘ + @WhereColoum + ‘ = ”’ + @WhereValue + ””
exec (@SQL)</pre>

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Eabouzeid
    Thanks for posting this. How about creating a stored procedure with a parameter for the table? In other words, the table will be named something different each time but the query remains the same.
    175 pointsBadges:
    report
  • carlosdl
    The concept is the same. In this case the complete SELECT statement is a string constructed in the procedure, so any part of it could be variable. You would just need to send the table name to the procedure as a parameter, and concatenate it where appropriate.
    69,920 pointsBadges:
    report
  • Eabouzeid
    Thank you Carlos. Can you give me a script example?
    175 pointsBadges:
    report
  • Eabouzeid
    While we're at it, do you think you could help with another post of mine? Here is what I asked? "I want to have a stored procedure run on a schedule that automatically creates a database output file as a result and saves it to a specific location on my network. Can someone please tell me how to do this?" Thanks again for your help with this.
    175 pointsBadges:
    report
  • carlosdl
    Here's the modified example:
    CREATE PROCEDURE YourProcedure
           @TableName sysname,
           @WhereColoum sysname,
           @WhereValue varchar(20)
     AS
     DECLARE @SQL nvarchar(4000)
     SET @SQL = 'SELECT *
     FROM ' + @TableName +
     ' WHERE ' + @WhereColoum + ' = ''' + @WhereValue + ''''
     exec (@SQL)
    69,920 pointsBadges:
    report
  • Eabouzeid
    Thanks Carlos. I'm getting an error message. Will this code work in TSQL? I'm getting a syntax error at "SET @SQL = ‘SELECT *"
    175 pointsBadges:
    report
  • carlosdl
    Yes, it is T-SQL code. Did you copy-pasted it ? If so, try replacing the quotes, as this editor converts them to other similar (but incorrect) character. Here's the code again (without using the code tool, and with a minor correction at the end of the sql string): CREATE PROCEDURE YourProcedure @TableName sysname, @WhereColoum sysname, @WhereValue varchar(20) AS DECLARE @SQL nvarchar(4000) SET @SQL = 'SELECT * FROM ' + @TableName + ' WHERE ' + @WhereColoum + ' = "' + @WhereValue + '"' exec (@SQL)
    69,920 pointsBadges:
    report
  • Eabouzeid
    Thank you. That fixed it. It was just a matter of the text editor.
    175 pointsBadges:
    report
  • Eabouzeid
    Carlos: Can you please help with this one: “I want to have a stored procedure run on a schedule that automatically creates a database output file as a result and saves it to a specific location on my network. Can someone please tell me how to do this?”
    175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following