Using IF/Set statements - 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 2000 Forums

SQL Server Development (2000)

Using IF/Set statements

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-19 : 12:24:20

<code>CREATE PROCEDURE Main @gno varchar(50), @gtype varchar(50), @gint smallint output, @typeint smallint output AS
DECLARE @gtime smalldatetime select @gtime[Gtime] from SB WHERE [IDNum] = @gno IF @gtime > GETDATE() SET @typeint = 1
SELECT @gint = COUNT(fld1) FROM TABLEFIVE WHERE (fld1) = @gno AND (fld2) = @gtype
</code>
The code below is giving me the problem. I am trying to get a time value from the SB table that matches the correct row number. Then if this time is later than the present time, I want to assign @typeint a value of 1. I send it to the sproc as 0. If it comes out at 1, I know that it is too late for any further manipulations. But I keep getting an error message that Cast from type DBNull to type Short is not valid. This only occurs when I am testing it and making certain that time in the table has passed the current time. Otherwise, I do get 0. I think?? my problem is with the IF..SET part?? Thanks for any help.
DECLARE @gtime smalldatetime select @gtime[Gtime] from SB WHERE [IDNum] = @gno IF @gtime > GETDATE() SET @typeint = 1

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-19 : 12:37:44

try this


CREATE PROCEDURE Main @gno varchar(50), @gtype varchar(50), @gint smallint output, @typeint smallint output 
AS 

DECLARE @gtime smalldatetime 

select @gtime = [Gtime] 
from SB 
WHERE [IDNum] = @gno 

IF @gtime > GETDATE() 
SET @typeint = 1 

SELECT @gint = COUNT(fld1) 
FROM TABLEFIVE 
WHERE (fld1) = @gno AND (fld2) = @gtype

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-19 : 12:40:34

is @gno guaranteed to exist in table SB?

If not then @gtime can be NULL

you'll have to use a case statement in your select or
and isnull in your if statement.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-19 : 12:47:59

I tried that, even though I think what I have is the same. Unless the way it is displayed matters. Thanks, but still wondering about this.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-19 : 12:50:04

have you tried debugging with QA to see what's happening?

Also it might be good to run a trace with the SQL profiler and then see what's being passed in for parameters.

Edited by - ValterBorges on 04/19/2003 12:54:42

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-19 : 13:01:17

Don't laugh too hard. I don't have EM or QA. I haven't been able to download that monster SQL. I have a dialup connection. Only thing available here in western Maryland. I have tried a couple times and it loses connection and then wants to start over. And it would take at least 24 hours to do it.
That code works fine for the part where I select a row count. The parameters are identical for it and the one giving me a headache. Sending in a smallint and returning one.
Thanks for all your help, sir. It always seems to work itself out. LOL.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-19 : 14:49:24

Get download accelerator from www.download.com

Then leave it downloading over night.
It will recover where it left off if you get disconnected.

You may also create a log table which just stores the parameters you pass in using an insert statement then you can read the log table and see what it is your passing in.

How come you have sql server but not the QA? What version? How did you install the server?

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-19 : 15:18:08

I am using a tool called Web Matrix to build a site with asp.net pages. I don't have SQL, just a facsimile, I guess that let's me use my local computer with a virtual server. It came with the Web Matrix download. Something tells me there is an easier way to do this, but I seem to be plugging along.
Thanks for the help. That download will be a blessing, I am sure.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-19 : 16:47:57

For what it is worth. I tried something else with this.
CREATE PROCEDURE TimeCheckProc @GameNo VarChar(50), @GameInt SMALLINT OUTPUT AS
DECLARE @gtime SMALLDATETIME
SELECT @gtime = [GTime] FROM SB WHERE [IDNum] = @GameNo
IF @gtime > GETDATE()
SET @GameInt = 1
======
If i remove the IF @gtime > GETDATE(), no sweat. It returns 1 to my vb.net code. Something is not right with my IF line of code. I am certain that my column in SB for GTime is SMALLDATETIME. Got me here.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-19 : 16:51:15

You need an ELSE or a default value for @GameInt. it is only set if the IF condition is true. If the condition is false, nothing happens and you never assign a value to @GameInt, so it is Null.

If @Gttime is NOT greater than getdate(), what should @GameInt be??

for example, something like:

IF @gtime > GETDATE()
SET @GameInt = 1
ELSE
SET @GameInt = 0

I hope this helps a little.

- Jeff

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-19 : 18:05:05

Thanks bigtime. Worked like a charm.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-19 : 18:46:33

You can do this without the variable

SELECT @GameInt = case when [GTime] > getdate() then 1 else 0 end
FROM SB
WHERE [IDNum] = @GameNo

or

select @GameInt = 0
SELECT @GameInt = 1
FROM SB
WHERE [IDNum] = @GameNo
and [GTime] > getdate()

or

if exists (select * FROM SB WHERE [IDNum] = @GameNo and [GTime] > getdate())
SELECT @GameInt = 1
else
SELECT @GameInt = 0

==========================================
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.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-19 : 23:20:18

Thanks much. Will try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources