May 14, 2002 at 8:14 am
I have inherited a chunk of code that does dynamic T-SQL. I want to get away from it for performance reasons. However, I haven't been able to figure out how to get past one spot.
A stored procedure I'm working with doesn't know which table it will be pulling data from when it runs. This means dynamic SQL is being used to create the FROM clause of the query. I tried a case statement yesterday like this, but it didn't work:
DECLARE @i
SET @i = 1
SELECT * FROM (
WHEN @i THEN table1
ELSE table2
END)
If it were just a matter of two tables, I would simply do an IF/ELSE with two hard coded queries. Instead, I have literally dozens of tables to pick from.
Any suggestions?
Steve
Steve Miller
May 14, 2002 at 8:50 am
You can't use a case to define the table choice. Dynamic SQL or using If Else logic and maybe multiple stored procedures for storing individual execution plans is your other option.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 9:01 am
Figured as much. Thanks.
Steve
Steve Miller
May 14, 2002 at 1:32 pm
If you are using SQL Server 2000
You could make your life simpler by writing a function that builds an SQL Statement for you, executes it and returns a table variable or a rowset.
Your call then could look something like this.
Select * from ::fn_Snapshot(@MyTableName)
Thou this will not improve performance.
quote:
Figured as much. Thanks.Steve
May 14, 2002 at 2:36 pm
I'm not sure I'm following. This works:
ALTER FUNCTION GetTableName (@str1 NVARCHAR(100), @str2 NVARCHAR(100))
RETURNS NVARCHAR(200)
AS
BEGIN
RETURN RTRIM(@str1) + @str2
END
GO
This works:
SELECT dbo.GetTableName('my', 'Table')
This does not:
SELECT * FROM (dbo.GetTableName('cm', 'Object') )
I tried various ways to work with your double colon syntax, but I couldn't make it work.
Our current procedure builds SQL code for us, runs it, and returns rows, as you suggested.
Steve
Steve Miller
May 15, 2002 at 8:23 am
I appologise made a typo, the only time you would use double colon is when you are calling a system table function like this.
select * from ::fn_helpcollations()
quote:
I'm not sure I'm following. This works:ALTER FUNCTION GetTableName (@str1 NVARCHAR(100), @str2 NVARCHAR(100))
RETURNS NVARCHAR(200)
AS
BEGIN
RETURN RTRIM(@str1) + @str2
END
GO
This works:
SELECT dbo.GetTableName('my', 'Table')
This does not:
SELECT * FROM (dbo.GetTableName('cm', 'Object') )
I tried various ways to work with your double colon syntax, but I couldn't make it work.
Our current procedure builds SQL code for us, runs it, and returns rows, as you suggested.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply