Defining a declared object as SQL in a procedure

[blom0344]

I have an T-SQL script that reads some 660 with many occurences of a certain date calculation:

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:

@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

 

[mrdenny]

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

 

[Qik3Coder]

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:

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.

...
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.