Problem with isnull. Need to substitute null if a var is null and compare it to null and return

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @.inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off? Thank you

set ansi_nulls off

go

declare

@.inFileName VARCHAR (100),

@.inFileSize INT,

@.Id int,

@.inlanguageid INT,

@.inFileVersion VARCHAR (100),

@.ExeState int

set @.inFileName = 'A0006337.EXE'

set @.inFileSize = 28796

set @.Id= 1

set @.inlanguageid =null

set @.inFileVersion =NULL

set @.ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @.inFileName

AND DR.FileSize =@.inFileSize

AND DR.FileVersion = isnull(@.inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@.inlanguageid,null)

AND DR.[ID]= @.ID

)

go

set ansi_nulls on

well actually you dont need to change the setting

if you're up to something like this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

|||

There is a slight problem with this. If the right side is null, it will evaluate to -1. If the left side is not null, it will evaluate to value stored in the table. It's VERY likely that the value in the table won't be -1. So the condition will be false. But, in actuality, it should be true, correct? Shouldn't it be like this?

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,isnull(DR.FileVersion,-1))

Thank you

|||

with this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

the ending equation would be

and (-1 = -1) which evaluates to true.

meaning null=null

remember that this equation resides in the "where clause" and not on the

select clause. if you want to have it returned you must

place a "case clause" in the select statement to evaluate this

nevertheless this clause must still exist in the where clause

to include the nulls

sql

Problem with getdate function in optional parameters

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.

I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.

ALTER PROCEDURE

[dbo].[LinksFees_Record]

@.Date

datetime=getdate

AS

INSERT INTOLinkSearchFees

(LinkID, Price, [Date])

SELECTIDASLinkID, SearchDayFeeASPrice, @.DateFROMLinksWHERE(SearchDayFee > 0)

RETURNWhen I call the StoredProcedure the following exception occur:Conversion failed when converting datetime from character string.

How can I fix it?

Hi!

Try this:

ALTER PROCEDURE

[dbo].[LinksFees_Record]
@.Date

datetime = NULL
AS

IF @.DATE IS NULL SET @.Date= getdate()
... rest of your procedure goes here ...

Now if the users passes no parameter then @.Date will be replaced by getdate() result. Beware that if the user passes a NULL it will also be replaced by GetDate() results.

Have a good day,

David

Problem with formula input with SQL Server Management Studio Express

Hi all,

I'm creating a database using SQL Server Management Studio Express and have a problem. I've got 4 columns: Surface, Rent, MonthlyIncome and AnnualIncome. Surface and Rent are inputed by user, MonthlyIncome is straight calculation Rent*Surface using Computed Column Formula. But, when I want to calculate AnnualIncome SQL SMSE doesn't allow me to input formula like this MonthlyIncome*12. Where I can read about limitations in formula field?

TIA.

Przemek

hi,

computed columns can not include other computed columns in their definition..

http://msdn2.microsoft.com/en-us/library/ms191250.aspx

but, as the definition of your AnnualIncom column is not that heavy ( ) you can define it on the base columns without problems as

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.t1 (

Id int NOT NULL PRIMARY KEY,

Surface int NOT NULL DEFAULT 0,

Rent decimal(18,4) NOT NULL DEFAULT 0,

MonthlyIncome AS (Surface * Rent),

AnnualIncome AS ((Surface * Rent) * 12 )

);

GO

INSERT INTO dbo.t1 VALUES ( 1 , 10, 1.8 );

SELECT * FROM dbo.t1;

GO

DROP TABLE dbo.t1;

--<-

Id Surface Rent MonthlyIncome AnnualIncome

-- -- --

1 10 1.8000 18.0000 216.0000

regards|||Hi Andrea, thank you very much for help. I shoul read msdn more carefully.

Przemek

Problem with Dynamic SQL !

Hi friends,

I have a procedure with an input parameter & output parameter. The input parameter value is a table name which has an identity column. The procedure will set the next value of the identity column to the output parameter. I stuck with the dynamic sql. Here it goes...

---------------------------------------
CREATE TABLE seqtest(nextVal NUMERIC(38) IDENTITY(1000000,1),dummyCol TINYINT);

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
EXECUTE( 'SELECT '+ @.nv +' = id from ' + @.seqName )
SET @.nextVal = @.nv
END

---------------------------------------
after i created the table & procedure, i executed the below code:
---------------------------------------
DECLARE @.nextVal1 int
EXECUTE NextVal 'seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1
---------------------------------------

but it says

Msg 170, Level 15, State 1, Server SWISSQL-WIN2K, Line 1
Line 1: Incorrect syntax near '='.
(return status = 0)

Can anyone point out where i went wrong?

JakeDECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||Hi Eniqma,

It didn't solve my prob. same error...
The problem is in the select statement
EXECUTE( 'SELECT '+ @.nv +' = nextVal from ' + @.seqName )
It says Incorrect syntax near '='.

any idea?

Jake|||Oops ... i forgot ... you cannot create a dynamic string inside an execute statement

You will have to do something like

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )
select @.query ='SELECT '+ @.nv +' = id from ' + @.seqName
EXECUTE(@.query )
SET @.nextVal = @.nv
END|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= @.@.identity

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= scope_identity()

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1



You should really be using scope_identity()

And from what I think what you are trying to achieve , it would not matter even if you used a identity column in your original table ...|||Hi eniqma, when the procedure is executed, it will say

Msg 245, Level 16, State 1, Server SWISSQL-WIN2K, Line 9
Syntax error converting the varchar value 'SELECT ' to a column of data type int.

as int is concatenated with string.

@.@.IDENTITY approach guides me to the solution. but i didn't use @.@.IDENTITY as it returns the last identity value generated for any table in the current session, across all scopes.
I used IDENT_CURRENT('table_name') as it returns the last identity value generated for a specific table in any session and any scope.

so here is the final procedure

---------------------------------------
alter procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
SET @.nextVal = IDENT_CURRENT(@.seqName)
END
---------------------------------------

thanks eniqma & harshal for your time & help.

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake

I think u have already answered the question.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

since the select and insert are not in the scope i think it wont work.|||Hmm ... you are right harshal ... never thought about that

Jake .. mind clarifying why you are going through all this when you could have done with a indentity column in the table for which you are generating a sequence ...|||i thought the dynamic sql execution would happen in the current scope. it puzzles me... so what actually happens is dynamic sql execution happens in a different scope than where it is called. may be i have to understand the execute statement further...

thanks for the clarification, harshal.

Jake|||oh! sorry, i missed that... i am trying to automate the SQL stored procedures conversion from Oracle to SQL Server. as you know Oracle has sequence & SQL Server doesn't.. That's why i trying to simulate sequence in SQL Server...
Thanks for your interest.

Jake|||Oh ... so thats what you are trying to do ...

Talking about scope ... its the same in sql as in other languages ...
If you called a stored procedure B inside a stored procedure A then the variables declared in sproc B get out of scope as soon as it returns control to sproc A. Similar with EXEC ... think of it as a stored procedure which executes what ever is passed to it and returning the result.|||now i understand, enigma... glad to see your reply.

Thanks,
Jake|||Without reading this little lot too deeply, the answer to the original question is:
1: Look at the spaces before and after you + signs '+ @.n +' needs to be:
' + @.n + '
2: You have to explicilty convert the int variable to a string (sorry, thats the vb in me coming out) varchar before you can add it to one.

E.G. 'I am a varchar ' + CAST(@.IntVariable AS Varchar(250)) + ' The rest of the varchar string'

Otherwise you get the converting int to varchar error.

Have fun
Best regards
Steve

P.S. Don't forget the spaces when breaking strings and inserting variables (before and after). Use Print CAST(@.SqlString as Varchar(250)) to check your Sequel statement for errors.

problem with datetime value as parameter value

Hi all,
I created a stored proc that has input datetime parameters (begindate and enddate), I tried the command: exec storedproc '20060320' in the query command part in Microsoft SQL Server Management Studio and it works but when I try to create a report dataset using the stored proc and execute it using value 20060320 it does not work. I even try using '20060320' and "20060320" as the value and it did not work also. I received the following error:
TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to convert parameter value from a String to a DateTime.

ADDITIONAL INFORMATION:
Failed to convert parameter value from a String to a DateTime. (System.Data)

String was not recognized as a valid DateTime. (mscorlib)

BUTTONS:
OK

Anyone have any idea on how can I solve it or go about it? Thanks in advance.
Daren
Try using the format: "mm/dd/yyyy", so it would be: 03/20/2006.|||Thanks Deepak,
This solved my problem.
Daren

Problem with Date logic

Hi OK, Here goes.
I have a booking table with a Checked In Date and a Checked Out Day. I have
a user input screen where a user Chooses a checked in date and checked out
date. I want to know if any rooms are available so I check against the
booking table. I've tried a between date but I have two dates going into th
e
Query and comparing it against the booking table which also has two dates.
Here is the stored procedure that works but only if I pass one date.
ALTER PROCEDURE dbo.Checkbookings
@.CheckIn datetime
AS
Insert Into booktbl select Book.ibook_site from tbl_booking as book
where @.Checkin between book.ibook_in_date and book.ibook_out_date
Select * from tbl_site where not exists(Select * from booktbl where
tbl_site.site_id = booktbl.ibook_site)
I'm trying not to loop in code through the time period the user has placed.
I would like to do the work on the SQL Server.
Any Help at all would be greatly appreciated.Vear
Please post DDL+ Sample data ?
Actually you can use
IF EXISTS (SELECT * FROM Booking WHERE dt>=@.date_in AND dt<
DATEADD(day,1,@.date_out)
Do something here
ELSE
Probably INSERT coomand
"Vear" <Vear@.discussions.microsoft.com> wrote in message
news:08FA718C-8B53-4E7C-977A-F2FFDCEBE01E@.microsoft.com...
> Hi OK, Here goes.
> I have a booking table with a Checked In Date and a Checked Out Day. I
> have
> a user input screen where a user Chooses a checked in date and checked out
> date. I want to know if any rooms are available so I check against the
> booking table. I've tried a between date but I have two dates going into
> the
> Query and comparing it against the booking table which also has two dates.
>
> Here is the stored procedure that works but only if I pass one date.
> ALTER PROCEDURE dbo.Checkbookings
> @.CheckIn datetime
> AS
> Insert Into booktbl select Book.ibook_site from tbl_booking as book
> where @.Checkin between book.ibook_in_date and book.ibook_out_date
> Select * from tbl_site where not exists(Select * from booktbl where
> tbl_site.site_id = booktbl.ibook_site)
> I'm trying not to loop in code through the time period the user has
> placed.
> I would like to do the work on the SQL Server.
> Any Help at all would be greatly appreciated.|||On Thu, 16 Mar 2006 18:47:28 -0800, Vear wrote:

>Hi OK, Here goes.
>I have a booking table with a Checked In Date and a Checked Out Day. I have
>a user input screen where a user Chooses a checked in date and checked out
>date. I want to know if any rooms are available so I check against the
>booking table. I've tried a between date but I have two dates going into t
he
>Query and comparing it against the booking table which also has two dates.
(snip)
Hi Vear,
You didn't post CREATE TABLE and INSERT statements to show how your
tables and data look like, so I'll have to make some assumptions. If you
have a fairly standard design for a reservations database, a query to
find rooms that are available in a give period would roughly look like
this:
SELECT r.RoomNo
FROM Rooms AS r
WHERE NOT EXISTS
(SELECT *
FROM Reservations AS res
WHERE res.RoomNo = r.RoomNo
AND res.EndDate > @.StartDate
AND res.StartDate < @.EndDate)
(Here, @.StartDate and @.EndDate are the period in which the room should
be free, and res.StartDate and res.EndDate are the start and end dates
of existing reservations).
Hugo Kornelis, SQL Server MVP|||Thanks for replying. I used the If Exists and it works great. I rotate
through the dates in the period I'm looking at and send it to a Temp table.
Thanks for your help
"Hugo Kornelis" wrote:

> On Thu, 16 Mar 2006 18:47:28 -0800, Vear wrote:
>
> (snip)
> Hi Vear,
> You didn't post CREATE TABLE and INSERT statements to show how your
> tables and data look like, so I'll have to make some assumptions. If you
> have a fairly standard design for a reservations database, a query to
> find rooms that are available in a give period would roughly look like
> this:
> SELECT r.RoomNo
> FROM Rooms AS r
> WHERE NOT EXISTS
> (SELECT *
> FROM Reservations AS res
> WHERE res.RoomNo = r.RoomNo
> AND res.EndDate > @.StartDate
> AND res.StartDate < @.EndDate)
> (Here, @.StartDate and @.EndDate are the period in which the room should
> be free, and res.StartDate and res.EndDate are the start and end dates
> of existing reservations).
> --
> Hugo Kornelis, SQL Server MVP
>