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 I want to restore a backup on a different computer?

Hi,
Please guide which options are important and should be selected if I
plan to restore the backup being taken to be eventually restored on a
computer which is not on the LAN.
Also, once the backup is done, will I be able to restore the backup if I
only have the MDF file or do i also need the LOG file?
Thanks.
Hi,
There are 2 options for you.
1. MDF and LDF files to destination
2. Backup the database copy the .BAK files to destination
1.
a. SP_detach_db to detach the database from source server
b. Copy the MDF and LDF to a new location and then execute sp_attach_db
to attach the database in source server
c. Send the file to destination server
d. Execute sp_attach_db to attach the database
2.
a. Backup the datbase using BACKUP DATABASE comand in source server
b. copy the .BAK file to destination
c. restore the database using RESTORE DATABASE comamnd
Note:
It is always safe to have MDF and LDF in first approach.
Refer books online for command usage.
Thanks
Hari
MCDBA
"Learner" <wantnospam@.email.com> wrote in message
news:MPG.1af64861bf5b817c9896fa@.msnews.microsoft.c om...
> Hi,
> Please guide which options are important and should be selected if I
> plan to restore the backup being taken to be eventually restored on a
> computer which is not on the LAN.
> Also, once the backup is done, will I be able to restore the backup if I
> only have the MDF file or do i also need the LOG file?
> Thanks.

If all values of dropdown parameter are selected - show ALL in report header

Hi,
I have dropdown parameter with multi-values allowed.
In my report headed I want to show all the dropdown values that were checked by the user to run the report. But since there could be a couple of hundred values I want to show ALL when all the values are selected instead of listing them one by one.
How can I do that?
Thanks,

Igor

Igor,

I recently had a similiar problem. While I'm sure that there is a more elegant solution, this seemed to work for me:

In the textbox where you are displaying the selected parameters you can write an expression that compares the selected item count from the parameter dropdown to the total count of the dropdowns data source.

For example, if you have a multi value parameter called Parm1 and it has a datasourse called Query1 (which selects a KeyID and TextValue) then the expression in your textbox might look something like:

=iif(Parameters!Parm1.Count = count(Fields!KeyID.Value, "Query1"),"All", join(Parameters!Parm1.Label,", "))

This above expression should display the word "All" if all values are seleted from the parameter drop down, otherwise it will list out the selected parameters individually.

Hope this helps.

|||

Hi,

I am getting error,Fields cannot be used on Header and footer.

Any work around.

Thanks

sql

If all values of dropdown parameter are selected - show ALL in report header

Hi,
I have dropdown parameter with multi-values allowed.
In my report headed I want to show all the dropdown values that were checked by the user to run the report. But since there could be a couple of hundred values I want to show ALL when all the values are selected instead of listing them one by one.
How can I do that?
Thanks,

Igor

Igor,

I recently had a similiar problem. While I'm sure that there is a more elegant solution, this seemed to work for me:

In the textbox where you are displaying the selected parameters you can write an expression that compares the selected item count from the parameter dropdown to the total count of the dropdowns data source.

For example, if you have a multi value parameter called Parm1 and it has a datasourse called Query1 (which selects a KeyID and TextValue) then the expression in your textbox might look something like:

=iif(Parameters!Parm1.Count = count(Fields!KeyID.Value, "Query1"),"All", join(Parameters!Parm1.Label,", "))

This above expression should display the word "All" if all values are seleted from the parameter drop down, otherwise it will list out the selected parameters individually.

Hope this helps.

|||

Hi,

I am getting error,Fields cannot be used on Header and footer.

Any work around.

Thanks

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.