SQL Server 2005: SQL statement in a variable

30 pts.
Tags:
SQL
SQL Server 2005
SQL statements
SQL variables
Can anybody help me? "HOW TO ASSIGN A SQL-STATEMENT IN A VARIABLE". FOR EXAMPLE : TO ASSIGN A STRING IN A VARIABLE, WE DO LIKE THIS: DECLARE @A AS VARCHAR(20) SET @A='HAI' PRINT @A OUTPUT: HAI SIMILARLY HOW TO ASSIGN A SQL-STATEMENT IN A VARIABLE.

Answer Wiki

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

This is the quick and dirty:

DECLARE
@SQL VARCHAR(8000)
, @SQLVariable INT

SET @SQLVariable = 1

SET @SQL = ‘SELECT * FROM Table WHERE Value = ‘ + CAST( @SQLVariable AS VARCHAR( 5 ) )

EXEC( @SQL )

For a more secure solution research sp_executesql in BOL.

Discuss This Question: 5  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
  • carlosdl
    You might want to share whith us the reasons why you want to store the statement in a variable. What are you planning to do ? You could store the statement as a string, and then execute it as dynamic sql.
    69,920 pointsBadges:
    report
  • Narendar
    Sir, In my code i am using a lot of sql statements which are repeated no. of times, so i thought why to repeat so many times instead of that i can assign the the sql statements in one one variable & use that varible wherever i need it, it makes my job is easy. Hope u got me. Narendar.
    30 pointsBadges:
    report
  • carlosdl
    You could put your SQL statements in a stored procedure. That would be better that using dynamic SQL for security reasons.
    69,920 pointsBadges:
    report
  • Narendar
    You could put your SQL statements in a stored procedure. That would be better that using dynamic SQL for security reasons. I am using sql statements in a stored procedure.
    30 pointsBadges:
    report
  • carlosdl
    You could put your repeating SQL statements in another procedure, so you can call it instead of writing the statements again every time you need them.
    69,920 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