If exists command returning an error

Hello, can anyone see a problem with this T-SQL?

1set ANSI_NULLSON2set QUOTED_IDENTIFIERON3GO4ALTER PROCEDURE [dbo].[Logon_P]5@.User_IDVARCHAR(50),6@.User_PasswordVARCHAR(50)7AS89IFEXISTS(SELECT 110FROM [User]11WHERE [User_Name] = @.User_ID)12BEGIN13RETURN 114IF ((SELECT User_PasswordFROM dbo.[User]WHERE [User_Name]) = @.User_ID) = @.User_Password15BEGIN16RETURN 217END18END19ELSE20RETURN 021

Its returning the following error:

Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

You have a RETURN in your IF..EXISTS block. SQL will return from the block as soon as it sees the RETURN. So the SELECT you have after the RETURN will not be executed. And I dont understand what you are trying to do with your second IF statement inside the first IF block..

|||

Hello my friend,

I have it working on my system. I amended it as follows: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[Logon_P]
(
@.User_ID VARCHAR(50),
@.User_Password VARCHAR(50)
)
AS

-- 0 = no such user
-- 1 = user exists but password incorrect
-- 2 = both user and password match
DECLARE @.Outcome AS TINYINT

IF EXISTS(SELECT 1 FROM [User] WHERE [User_Name] = @.User_ID)
BEGIN
SET @.Outcome = 1

IF EXISTS(SELECT 1 FROM [User] WHERE [User_Name] = @.User_ID AND User_Password = @.User_Password)
BEGIN
SET @.Outcome = 2
END
END
ELSE BEGIN
SET @.Outcome = 0
END

SELECT @.Outcome

In my database, I had a user of scott and a password of blue, so I tested it with the following: -

exec [Logon_P] 'scott', 'blue' -- returns 2

exec [Logon_P] 'scott', 'blue2' -- returns 1

exec [Logon_P] 'scotty', 'blue' -- returns 0

Kind regards

Scotty

|||

Thank you scotty, thats been a massive help.

IF ELSE with WHERE, AND, OR

What would be the correct way of writing a sql select state with where
clause while also using IF ELSE. I am using T-SQL and I cannot get it
to work. I probably have the syntax wrong.

I want to be able to have different where/and/or clauses in the sql
dependant on what value was passed into the @.SearchTerm parameter in
this stored procedure.

Can I use CASE statements in the WHERE section? Or is that strickly for
SELECT statements?

Code as follows:

================================================== ==============

CREATE PROCEDURE spTicketReport
(
@.SearchTerm varchar(100) = NULL
)
AS
BEGIN
SELECT TOP 100 PERCENT Tickets.TicketID, Tickets.TicketNumber AS
TicketNumber, Haulers.Name AS Hauler, Leases.LeaseID AS LeaseID,
Leases.LeaseName AS Lease, Shippers.Name AS
Shipper, Tickets.FeeTox, Tickets.FeeWashout, Tickets.FeeDisposal,
Tickets.Yards, Tickets.Barrels,
Tickets.FluidSolidRatio, DATEPART(yyyy, Tickets.DateAdded) AS [Year]
FROM Tickets INNER JOIN
Leases ON Tickets.LeaseID = Leases.LeaseID INNER
JOIN
Haulers ON Tickets.HaulerID = Haulers.HaulerID
INNER JOIN
Shippers ON Tickets.ShipperID =
Shippers.ShipperID
WHERE TicketNumber LIKE '%' + @.SearchTerm + '%' OR Haulers.Name LIKE
'%' + @.SearchTerm + '%' OR Shippers.Name LIKE '%' + @.SearchTerm + '%'
OR Leases.LeaseName LIKE '%' + @.SearchTerm + '%'
ORDER BY TicketNumber, Shipper, Hauler

================================================== ==============

Thanks in advance!

Jason Cochran
Rethink Technologies, L.L.C.
www.rethinkllc.com(jcochran@.rethinkllc.com) writes:
> What would be the correct way of writing a sql select state with where
> clause while also using IF ELSE. I am using T-SQL and I cannot get it
> to work. I probably have the syntax wrong.
> I want to be able to have different where/and/or clauses in the sql
> dependant on what value was passed into the @.SearchTerm parameter in
> this stored procedure.
> Can I use CASE statements in the WHERE section? Or is that strickly for
> SELECT statements?

You cannot use CASE statements, because there are none in T-SQL. But
you can use CASE expressions in a WHERE clause:

WHERE CASE WHEN @.SearchTerm LIKE <a ticket number>
THEN TicketNumber LIKE '%' + @.SearchTerm + '%'
WHEN @.SearchTerm LIKE <a haluers name>
THEN Haulers.Name
ELSE Leases.LeaseName
END LIKE '%' + @.SearchTerm + '%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is a good piece of information, however it can be achieved by the
other way as well...
By using parantheses and boolean operators (AND, OR, NOT) properly.

Something like this:

WHERE ( @.SearchTerm LIKE <a ticket number> ANDTicketNumber LIKE '%'
+ @.SearchTerm + '%' )
OR
( @.SearchTerm LIKE <a haluers name> ANDHaulers.Name LIKE '%' +
@.SearchTerm + '%' )
OR
( Leases.LeaseName LIKE '%' + @.SearchTerm + '%' )|||Hi Erland,
Very informative answer , but from performance point of view we should
not be using Like

Most DBMSs will use an index for a LIKE pattern if it starts with a
real character but will avoid an index for a LIKE pattern that starts
with a wildcard (either % or _). The only DBMSs that never use indexes
for LIKE are Pick and mSQL (on TEXT fields). For example, if the search
condition is:

... WHERE column1 LIKE 'C_F%'

DBMSs will resolve it by finding all index keys that start with C and
then filtering those that contain F in the third position. In other
words, you don't need to transform this search condition:
Here '%' is being used at the beginning so I think using charindex will
do fine job (Please correct it if wrong)

Wherecharindex
( @.SearchTerm,
(
CASE
WHEN charindex(@.SearchTerm, a ticket number )>0 THEN TicketNumber

WHEN charindex(@.SearchTerm,a haluers name) > 0 THEN Haulers.Name

ELSE Leases.LeaseName
END
)
)>0

With warm regards
Jatinder|||I appreciate everyones help on this.

What if I wanted to add another parameter named @.AccountID. AccountID
is used to track who created the ticket. @.AccountID would be set to
NULL just like @.SearchTerm is. I wanted to be able to check to see if
either was passed in. Sometimes both will be; other times it will be
either/or.

============ PSEUDO CODE ===================

WHERE TicketID != 0

IF NOT @.SearchTerm IS NULL THEN
AND (TicketNumber LIKE '%' + @.SearchTerm + '%' OR Haulers.Name
LIKE '%' + @.SearchTerm + '%' OR Shippers.Name LIKE '%' + @.SearchTerm +
'%' OR Leases.LeaseName LIKE '%' + @.SearchTerm + '%' )
END IF
IF NOT @.AccountID IS NULL THEN
AND AccountID = @.AccountID
END IF

ORDER BY TicketNumber, Shipper, Hauler

============ END PSEUDO CODE ===================|||(jcochran@.rethinkllc.com) writes:
> I appreciate everyones help on this.
> What if I wanted to add another parameter named @.AccountID. AccountID
> is used to track who created the ticket. @.AccountID would be set to
> NULL just like @.SearchTerm is. I wanted to be able to check to see if
> either was passed in. Sometimes both will be; other times it will be
> either/or.
>...
> IF NOT @.AccountID IS NULL THEN
> AND AccountID = @.AccountID
> END IF

AND (AccountID = @.AccountID OR @.AccountID IS NULL)

However, beware that if you want any index on AccuontID to be use, you
better split this up and have two different SELECT statements.

For a much longer discussion on a problem which you have not really
reached, but seem to be on your way to, I have an article on my web
site that you can save for a rainy day:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Looking at that article you mentioned; under if statements, the code
below is mentioned. It just seems like a very nasty way of doing
things. I could do it this way BUT I just think there should be a much
cleaner way of doing it. If I had to change/remove/add a column in the
select statement, I would have 3 other places to do it in. The WHERE
statement should be the only thing that is different. I shouldnt have
to have the same select statement 3 times.

IF @.orderid IS NOT NULL
BEGIN
SELECT ...
WHERE o.OrderID = @.orderid
AND od.OrderID = @.orderid
AND (od.UnitPrice >= @.minprice OR @.minprice IS NULL)
AND (od.UnitPrice <= @.maxprice OR @.maxprice IS NULL)
AND (od.ProductID = @.prodid OR @.prodid IS NULL)
AND (p.ProductName LIKE @.prodname + '%' OR @.prodname IS NULL)
ORDER BY o.OrderID
END
ELSE IF @.custid IS NOT NULL
BEGIN
SELECT ...
WHERE (o.OrderDate >= @.fromdate OR @.fromdate IS NULL)
AND (o.OrderDate <= @.todate OR @.todate IS NULL)
AND (od.UnitPrice >= @.minprice OR @.minprice IS NULL)
AND (od.UnitPrice <= @.maxprice OR @.maxprice IS NULL)
AND c.CustomerID = @.custid
AND o.CustomerID = @.custid
AND (od.ProductID = @.prodid OR @.prodid IS NULL)
AND (p.ProductName LIKE @.prodname + '%' OR @.prodname IS NULL)
ORDER BY o.OrderID
END
ELSE
BEGIN
SELECT ...
WHERE (o.OrderDate >= @.fromdate OR @.fromdate IS NULL)
AND (o.OrderDate <= @.todate OR @.todate IS NULL)
AND (od.UnitPrice >= @.minprice OR @.minprice IS NULL)
AND (od.UnitPrice <= @.maxprice OR @.maxprice IS NULL)
AND (c.CompanyName LIKE @.custname + '%' OR @.custname IS NULL)
AND (c.City = @.city OR @.city IS NULL)
AND (c.Region = @.region OR @.region IS NULL)
AND (c.Country = @.country OR @.country IS NULL)
AND (od.ProductID = @.prodid OR @.prodid IS NULL)
AND (p.ProductName LIKE @.prodname + '%' OR @.prodname IS NULL)
ORDER BY o.OrderID
END|||jcochran@.rethinkllc.com (jcochran@.rethinkllc.com) writes:
> Looking at that article you mentioned; under if statements, the code
> below is mentioned. It just seems like a very nasty way of doing
> things.

This is indeed not a method that scales well in terms of maintenance
when you have many different conditions, and I also note this in the
article.

> I could do it this way BUT I just think there should be a much
> cleaner way of doing it. If I had to change/remove/add a column in the
> select statement, I would have 3 other places to do it in. The WHERE
> statement should be the only thing that is different. I shouldnt have
> to have the same select statement 3 times.

Well, it depends with you mean with cleaner. You can do all in one
single static SQL statement, and from the perspective of maintenance
and functionality you would be fine. However, SQL programming is also
a lot about performance. If your table has 100 million rows, you don't
want a table scan to happen on an interactive query.

For this reason, one sometimes has to duplicate code in a way that
conflicts with the best practices you've learnt when working with
traditional languages.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

If conditional problem in T-Sql

I encounter a T-Sql problem related to if conditional processing:

The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.

Could anyone offer some advice?

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.

|||

Well the code that you have written is fine it should work perfectly.

Can you provide the code that you are using for droping the column of the table ?

|||

Alternatively if you want to check for existence of a column you could query the syscoumns table:

IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))
Begin
 --Do your insert
End

|||

Hi,

Thanks for your alternative way of querying system table for column existence.

However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.

This is a very strange phenomena.

- Yubo

|||Can you repost your new query and the error message pls?|||

From your earlier which I am copy pasting here:

**************************************************

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

***************************************************

It shows that ReportPreferences is the name of your table while the column name isReportTitle

While if you have just copy pasted the querry from ndinakar which is :

***************************************************

IF

EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))

Begin

-- Do your insert

End

***************************************************

The sequence of the name of the table is wrong.

Please try this instead and I am sure your problem would be solved :) .

If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End

And if this post does answer your question please dont hesitate to mark it as Answer.

Regards,

sql

Identity_insert not happening

I am setting insert_identity to on for a table in t-sql.
The table name is passed as a parameter in the tsql procedure.

When i write the following code.
set @.setStr = 'set IDENTITY_INSERT ' + @.toTableName +' ON'
execute (@.setStr)

and then set the insert query and execute it as follows:

set @.insQuery = 'insert into ' + @.toTableName + ' ( ' + @.colString + ') select ' + @.colString + ' from ' + @.fromTableName
execute(@.insQuery)

when i execute the procedure it doesnt insert values into the table and gives the following error though i am setting the identity to on.

Error: cannot insert explicit value for identity column in table 'emp' when IDENTITY_INSERT is set to OFF.

i cant make out why it is not applying identity_insert to the table.
Can anybody help me out.

Thank YouDon't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

macka.|||it doesnt work... :(
beacuse i've to use exec to execute the sp_executesql proc.
so it gives the same result..

so i can try to make one string by putting a newline character between the following 2 strings... and then just run one string... i guess it might be possible..

'set IDENTITY_INSERT ' + @.toTableName +' ON'

and

'insert into ' + @.toTableName + ' ( ' + @.colString + ') select ' + @.colString + ' from ' + @.fromTableName

but the problem is that i dont know how to append a newline character in the string \r \n \\r \\n dont work... can somebody suggest something on this...

Originally posted by macka
Don't use EXECUTE as it will run in a different thread/process to the rest of your code - so the code which follows the call, doesn't know anything about the fact you have set IDENTITY_INSERT to ON.

Try using sp_executesql instead. (Books Online has more information on how to use this system stored proc)

macka.|||Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

macka.|||Thanks for this.. i really appriciate ur help...
space works and actually newline character is char(10).. it works with this too... :)

Originally posted by macka
Why not just build it as a single string with space between the statements ? I've just tested that and it works fine.

macka.

Identity Columns

I had a table with identity column.
started dumping of data into it.
After that i want to drop the identity constraint on that column using t-sql script.

is it possible? if so how can i do it.

Nope, you cannot remove the identity property from a column. What you can do is to transfer the values to a different column and drop the column:

create table testIdentity
(
identityColumn int identity
)
go
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
go
alter table testIdentity
add nonIdentityColumn int null
go
update testIdentity
set nonIdentityColumn = identityColumn
go
alter table testIdentity
drop column identityColumn
go
select *
from testIdentity

|||Thanks.

But i would like to know why it is not possible through t-sql while we are able to do it from EM|||

Because EM does something along the lines of what I did. EM actually is pretty inefficient in many cases in how it performs operations (like adding a new column it does a drop column then and adds a new column where an ALTER TABLE would suffice). But it is usually understandable as it takes the easiest, most straightforward path for automation, rather than a method that looks better but cannot be automated quite as easy.

One thing to try is to trace what EM does using profiler. That is a great place to see the queries it does. In Management Studio, I changed the identity property, and these are the action queries that were sent:

CREATE TABLE dbo.Tmp_testIdentity
(
IdentityColumn int NOT NULL
) ON [PRIMARY]

go
IF EXISTS(SELECT * FROM dbo.testIdentity)
EXEC('INSERT INTO dbo.Tmp_testIdentity (IdentityColumn)
SELECT IdentityColumn FROM dbo.testIdentity WITH (HOLDLOCK TABLOCKX)')

go
DROP TABLE dbo.testIdentity

go
EXECUTE sp_rename N'dbo.Tmp_testIdentity', N'testIdentity', 'OBJECT'

go

The method I gave you is far less distructive, but either will work.