Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Defining a declared object as SQL in a procedure

Status
Not open for further replies.

blom0344

Technical User
Mar 20, 2002
3,441
NL
I have an T-SQL script that reads some 660 with many occurences of a certain date calculation:

Code:
CONVERT(DATETIME, CONVERT(VARCHAR(23), rdb.period_year * 10000 + rdb.period_month * 100 + 1), 112)

Where rdb is the table alias used furtheron in the script.

Can I define a variable like:

Code:
@date_from_year_month varchar(200)
set @date_from_year_month = 'CONVERT(DATETIME, CONVERT(VARCHAR(23), rdb.period_year * 10000 + rdb.period_month * 100 + 1), 112)'

And the use the declared variable in the sql part of the procedure in the next steps?

Ties Blom

 
Not without using dymanic SQL later on which will give you performance and security issues.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog
 
To put what MrDenny said in a positive light.

Yes it is possible. keep in mind that there are usually ways to do what you want to do without dynamic SQL.

You would do it with something like:
Code:
declare @var, @var2 varchar(8000)
set @var = ' And t.col1 = t.col1 '
set @var2 = 'Select * from table1 t where ' + @var + @var
exec(@var2)

For example, it may be more convenient to create a user defined function that returns the value of the convert that you were doing, then just reference that value.

Code:
...
where
...
and t.columnValue = dbo.udfMyConvertFunction(rdb.periodYear, rdb.periodMonth, rdb.periodDay)
...

Because the function will be compiled this will execute faster.

-The answer to your problem may not be the answer to your question.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top