Dynamic SQL assign variable

Question

Dynamic SQL assign variable

hamzajosh

SSChasing Mays

Points: 632
More actions
January 24, 2011 at 2:24 pm

#388707

I am trying to create a stored procedure that takes a database name as variable. I am trying to use this name to assign a value from a table to another variable but can't get it to work.
Below is code I am using.
Create procedure Vendors @GPCompany char(5)
as
Declare @SQL varchar(1000)
Declare @Location char(5)
set @SQL = 'set @Location = Select location from Accts where GPDB = ' + @GPCompany
Exec (@SQL)
but it gives error saying @Location has to be defined.
How do I assign a value to this variable using Dynamic SQL
I need to use this variable to make another dynamics SQL query to get other data.
Please help I am stuck here.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Hmm, this is the second question today on this same topic - is this homework?

You need to look into the scope of variables, and the usage of sp_executesql. I'll help out more if it's not a homework question.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

Derrick Smith

SSChampion

Points: 10833

January 24, 2011 at 3:35 pm

#1277502

Why are you using dynamic sql for this?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I have to ask the same question, why the dynamic sql. From what I can see it isn't needed.

hamzajosh SSChasing Mays Points: 632 More actions · Answer 4

Nope, this is not homework. I did look at sp_executesql but could not get it to work. How would I assign this variable the value I need from the dynamic SQL query.

hamzajosh SSChasing Mays Points: 632 More actions · Answer 5

Can you give me some code without using dynamic SQL for this?

Derrick Smith SSChampion Points: 10833 More actions · Answer 6

Sure. You're overthinking it:

CREATE PROCEDURE Vendors @GPCompany CHAR(5)

AS

BEGIN

DECLARE @Location CHAR(5)

SET @Location = (SELECT [Location]

FROM Accts

WHERE GPDB = @GPCompany)

SELECT @Location

END

GO