Dynamic SQL question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL question

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-10-29 : 16:05:08

I need to write a stored proc that will accept a table as a parameter. The first thing is it will see if a value exists in that table or not and will do something based on whether or not this value exists:

	create table #RayTable (Ray varchar(50))

	set @sqlCommand = 
			'insert into #RayTable select [' + @RayColumn + ']' + 
			'from [' + @Table + '] WHERE [' + @RayColumn + '] = ''' + @Ray + ''' '
	EXECUTE sp_executesql @sqlCommand
	set @ExistingRay = (select Ray from #RayTable)

The problem is that using a Temp Table is a little slow. I can't use a Table Variable because they don't work with dynamic SQL. I can't use @ExistingRay in the dynamic statement because it would be outside the scope of the Stored Procedure.

Anyone have any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2010-10-29 : 16:16:02

you can use a table variable

insert @t
exec (@sql)

I suspect the slowness is due to @RayColumn not being indexed.
Could maybe just select the first row (top 1) if you need the value or do an "if exists". These should both stop as soon as an entry is found rather than scanning the whole table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-10-29 : 16:21:44

Thankyou but when I use TableVariable I get:

Must declare the table variable "@RayTable".

I guess this is because the TableVariable exists in a different scope from the Dynamic call. But if I were to put the declaration of the TableVariable in the Dynamic call then I don't think I'll be able to retrieve it in the Stored Proc.

@RayColumn is indexed.

I just tried with the top 1. It didn't help in the example I was trying but I bet it wouold help with the larger tables, thankyou.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 16:33:10

it's not in a different scope

declare @RayTable table (Ray varchar(50))

set @sqlCommand =
'select [' + @RayColumn + ']' +
'from [' + @Table + '] WHERE [' + @RayColumn + '] = ''' + @Ray + ''' '
insert @RayTable
EXECUTE sp_executesql @sqlCommand
set @ExistingRay = (select Ray from @RayTable)

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-10-29 : 16:41:35

quote:
Originally posted by nigelrivett

it's not in a different scope

According to this article it is in a different scope.

[url]http://odetocode.com/Articles/365.aspx[/url]

Maybe we are using different terminology but bottome line is I get the error 'Must declare the table variable "@RayTable". Or does you version of SQL Server allow this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 16:42:05

As @RayColumn is indexed then you probably need to check why it is slow.
If it is the creation of the temp table then you are presumablt executing this many times for this to make a difference and might want to investigate other methods of implementing it (redesign).
Could be the amount of data inserted into the temp table.
Also check if it is using the index - if it's not covering it might not be.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-10-29 : 16:47:54

quote:
Originally posted by nigelrivett

As @RayColumn is indexed then you probably need to check why it is slow.
If it is the creation of the temp table then you are presumablt executing this many times for this to make a difference and might want to investigate other methods of implementing it (redesign).
Could be the amount of data inserted into the temp table.
Also check if it is using the index - if it's not covering it might not be.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Actually it is a redesign, we are trying to use this to replace a couple-hundred stored procedures but that's another story. It's only taking 4 milliseconds but the stored procedure this is replacing was taking under 1 millisecond. And yes, this is something that could get called often.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 16:52:37

quote:
Originally posted by denis_the_thief

quote:
Originally posted by nigelrivett

it's not in a different scope

According to this article it is in a different scope.

[url]http://odetocode.com/Articles/365.aspx[/url]

Maybe we are using different terminology but bottome line is I get the error 'Must declare the table variable "@RayTable". Or does you version of SQL Server allow this?

That is correct. A Table Variable will only be in scope if it is declared in the dynamic sql that is executing.

Do you only care about one row? You are inserting (presumably) multiple rows. But, the assignment to @ExistingRay will only assign the last row that appears in the set.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-10-29 : 16:57:39

quote:
Originally posted by Lamprey
...

Do you only care about one row? You are inserting (presumably) multiple rows. But, the assignment to @ExistingRay will only assign the last row that appears in the set.

It is just 1 row. @RayColumn is the PK. Basically, I am sent the Table, column (PK) and value as a parameter (among other parameters). And I only need to check if that value exists in the table, then do some other stuff based on wether or not it exists.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 16:58:12

declare @t table (s varchar(10))
declare @s varchar(1000)
select @s = 'select s=''nigel'''
insert @t
exec (@s)

It's only out of scope if you reference the table variable in the dynamic sql - you should be able to do the select in the dynamic sql and insert that into the table variable.
I'm pretty sure this was available in v2005.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 17:07:03

quote:
Originally posted by nigelrivett

declare @t table (s varchar(10))
declare @s varchar(1000)
select @s = 'select s=''nigel'''
insert @t
exec (@s)

It's only out of scope if you reference the table variable in the dynamic sql - you should be able to do the select in the dynamic sql and insert that into the table variable.
I'm pretty sure this was available in v2005.

AHh, yeah. I see what you are saying now and that will work. I guess it is an option to try. But, I doubt that inserting one row into a temp table versus a table variable is going to gain much performance...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 18:39:21

We're talking millisecond execution times so the table variable might have an effect.

If the SPs are that much faster and it's an issue - and not due to the SP being cached then I would consider creating the SP dynamically. Check if the SP exists for this column and if so execute it - if not then create the SP the execute it. should only be slow at the first execution - but then I would expect a similar effect from the statement query plan being cached. Might be worth looking at the cache misses.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-01 : 09:57:52

quote:
Originally posted by nr

you can use a table variable

insert @t
exec (@sql)

Oh sorry, I get it now. I thought that was 2 statements and the insert @t was just part of the statement. I had used that trick (an insert.. exec... though not involving Dynamic) before but only once and that was over 2 years ago. So thanks. This works now. Went from app. 4~~000~~ milliseconds to app. 1~~000~~ milliseconds - 4 times faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources