If Nothing Selected Into Variable What Is The Value Of Variable?

DECLARE @.PayTypeValue numeric (18, 5)
SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)
IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?
NULL OR 0DECLARE @.PayTypeValue numeric (18, 5)

SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)

IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?

NULL OR 0

why don't you try it and see for yourself:

SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue|||Also, try
SET @.PayTypeValue = 42
SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue

and see what happens.|||i thought it would return NULL.....|||But it returns...

The answer would be useful for people with similar questions ;)|||The answer is 42.
If the query returns no rows, the variable keeps its old value.|||Ahh that's clever - kinda like having an "else" value.
Cheers ivon|||hmmm...

That could be dangerous if you were using that variable in a loop.
I would wrap the SQL in ISNULL()

set @.somevariable=ISNULL((select somevalue from sometable),-9999999)
If @.somevariable=-9999999
'no rows were returned
Else|||That could be dangerous if you were using that variable in a loop.

How so? It retained it's initially declared value...
If you looped round, @.PayTypeValue would equal 42 each time..?|||Yes, but if you were expecting the value to change with each iteration...
What is returned from the select statement is also dependant on
@.compdiv and @.loadpay, so if those values change, it's quite possilbe that @.paytypevalue would change, too.|||Yes, but if you were expecting the value to change with each iteration...
That's how I found out; I got some very peculiar results.

BTW using IsNull() won't help: the query returns no rows, so there is no null value to replace with something else.

Solutions I'm using are
- Setting the variable to a default value before filling it with the query,
- Checking @.@.ROWCOUNT to see if any rows were returned from the query.|||I think it all depends on how you put it together.

declare @.tmp integer
set @.tmp=42
set @.tmp=isnull((select 18 where 1=0),0)
select @.tmp

Returns 0, which is what I'd expect, since
SELECT 18 WHERE 1=0
returns no rows|||How do I use @.@.ROWCOUNT to do this?

I haven't set this variable to anything before this and at least this instance doesn't loop. This SP is called once per order and checks 14 different things. Then feeds the results into a verification table. Those results and then selected by Crystal Reports and displayed to the user for an entire batch of orders one at a time by order number.|||RedNeckGeek: I now see that you put the isnull around the entire query.

tdecker81:
Something like
SELECT @.l_var = value
FROM table
WHERE <conditions>

SELECT @.l_records = @.@.ROWCOUNT

IF @.l_records > 0
BEGIN
' Do stuff with @.l_var
END|||Interesting. Since I rarely use the first syntax, I was unaware of this behavior:
set nocount on

declare @.MyValue int
set @.MyValue = 42

--This method retains @.MyValue
select @.MyValue = id from sysobjects where 1 = 0
select @.MyValue as 'Unchanged!'

--This method sets @.MyValue to NULL
set @.MyValue = (select id from sysobjects where 1 = 0)
select @.MyValue as 'Set to NULL!'

Learn something new every day...|||Learn something new every day...

my problem is I forget 2 somethings every day, so I think I'm losing the battle...

(most of it is stuff my wife tells me so I guess it's ok)

:)|||I'm sure you're wife will be there to constantly remind you too.sql

If Isnull Question

I have a SP I am trying to set a value for a variable in. The default value
for the variable is Null.
If there is not parameter passed I want to do an If Isnull statement to set
the variable in the SP, except I keep recveiving an incorrect Syntax error.
If any one can suggest a fix I would greatly appreciate it.
Here is my statement
If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
'Citi Special Servicing', 'HUD601SalomonDeal3'))
Thanks
-ChrisYou should look up the IF statement in Books Online, also look at how
variables should be checked for null values.

> If IsNull(Convert(VarChar(50), @.Client), Set @.Client in ('HUD601Solomon',
> 'Citi Special Servicing', 'HUD601SalomonDeal3'))
Is this a part of a query? Then you should also look up the CASE expression
in Books Online.
After you're done with BOL, check out this recent thread:
http://msdn.microsoft.com/newsgroup...5f-b8940be08178
I'm guessing you want something like that.
ML
http://milambda.blogspot.com/|||you want "if @.Client is null. "
isnull is a function which will replace a null occurence with a value
"@.Client is null" is using the is keyword, which will do an equality
check(ish - null != null).
beware not to use if @.Client = null as this will not work.sql

IDIOT NEEDS HELP comparing a variable against a list

I have a variable called @.ORComm which has been selected using a cursor from each line on an order.

DECLARE TC2 CURSOR FOR
SELECT [Commodity],[Total] FROM [CSITSS].[dbo].[Ordrate] WHERE [OrderNumber]= @.OrdNum AND [Companydiv] = 'GLPC-TRANS'
OPEN TC2
FETCH NEXT FROM TC2 INTO @.ORComm, @.ORTotal

I need to compare the resulting @.ORComm against a list of valid commodity types selectable by

SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0

What's the easiest way to do this?SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0 and CommodityClass=@.ORComm

??|||I feel stupid I didn't think of that. I was thinking of storing the selected values in an array but that is much simpler.

Identity Function

I don't think this is possible as i've tried testing but just wanted to make sure.

Can you use a variable within the Identity Function? ie., Identity(int, @.start, 1)

If you can't is there another function to perform what I am trying to do?

Basically I would like to imitate an identity column to avoid mass updates and re-create tables that had an identity column.

Any help / advice would be greatly appreciated.

If I understood correctly, you want the identity value to start from a certain number and then increment by 1?

You have some options for that:
Set the initial number of the identity column to the desired. E.g. start from 2000.