variables - 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 2012 Forums

Transact-SQL (2012)

variables

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-07-18 : 13:11:37

hi
how can i get this to work
DECLARE @FK_DataLoadBatch INT;

SET @FK_DataLoadBatch =(
SELECT PK_DataLoadBatch
FROM DataLoadBatch
WHERE Status IN ('In Process')
AND LoadType = 'gl')

i want to set @FK_DataLoadBatch equal to the number from PK_DataLoadBatch

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-18 : 13:24:35

quote:
Originally posted by rjhe22

hi
how can i get this to work
DECLARE @FK_DataLoadBatch INT;

SET @FK_DataLoadBatch =(
SELECT PK_DataLoadBatch
FROM DataLoadBatch
WHERE Status IN ('In Process')
AND LoadType = 'gl')

i want to set @FK_DataLoadBatch equal to the number from PK_DataLoadBatch

thanks

What you have there would work only if it is guaranteed that the inner select returns a single row. Otherwise you will get an error. That is so because the variable @FK_DataLoadBatch is a scalar variable - it can hold just one integer value. So if the inner query returns more than one value, SQL Server doesn't know what to do with it, and hence the error.

Given that you might get more than one PK_DataLoadBatch from your inner query, which of those values do you want to assign to the variable? You can choose one in any number of ways - by narrowing the where clause, or picking one randomly etc. If you want to pick one randomly, add a TOP clause like so:

DECLARE @FK_DataLoadBatch INT;

SET @FK_DataLoadBatch =(
SELECT TOP (1) PK_DataLoadBatch
FROM DataLoadBatch
WHERE Status IN ('In Process')
AND LoadType = 'gl')

You can also write the code like shown below, but the important thing is to decide which of the multiple values you want to pick

DECLARE @FK_DataLoadBatch INT;

SELECT TOP(1) @FK_DataLoadBatch = PK_DataLoadBatch
FROM DataLoadBatch
WHERE Status IN ('In Process')
AND LoadType = 'gl'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources