dearnemo
asked on
SQL Server scalar function
Hi,
I want a scalar function in SQL Server that checks if ID exists in the table, if it does then find if userid for that ID exists. If both are true then return 1 else return 0.
Here's my code:
I m getting
Msg 156, Level 15, State 1, Procedure ufnCheckDistrictAndntid, Line 7
Incorrect syntax near the keyword 'IF'.
I m a newbie using SQL function. ANy clue to resolve it? Thanks.
I want a scalar function in SQL Server that checks if ID exists in the table, if it does then find if userid for that ID exists. If both are true then return 1 else return 0.
Here's my code:
CREATE FUNCTION dbo.ufnCheckIDAndUSERID(@ID varchar)
RETURNS bit
AS
BEGIN
--first check if the ID is there in SQL table:
IF ((SELECT count(ID) from [mydb].[dbo].[t] where ID = @ID) = 1)
AND
((SELECT count(UserID) from [mydb].[dbo].[t] where ID = @ID)=1)
RETURN 1
ELSE
RETURN 0
END;
I m getting
Msg 156, Level 15, State 1, Procedure ufnCheckDistrictAndntid, Line 7
Incorrect syntax near the keyword 'IF'.
I m a newbie using SQL function. ANy clue to resolve it? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No worries, actually just realised if you use my code you need to add a line after declare...
set @Result = 0
set @Result = 0
Is the field [t].[ID] bigint or varchar?
if it is a big integer then your parameter should be bigint
why scan the table twice?
if it is a big integer then your parameter should be bigint
why scan the table twice?
CREATE TABLE T
([ID] int, [UserID] varchar(5))
;
INSERT INTO T
([ID], [UserID])
VALUES
(101, 'Fred'),
(102, 'Wilma')
;
**Query 1**:
declare @Id varchar(38)
set @Id = '101'
SELECT count(ID) from [dbo].[t] where ID = @ID
union all
SELECT count(UserID) from [dbo].[t] where ID = @ID
**[Results][2]**:
| COLUMN_0 |
|----------|
| 1 |
| 1 |
**Query 2**:
declare @Id varchar(38)
set @Id = '101'
SELECT
count(id) + count(UserID)
from [dbo].[t]
where id = @id
**[Results][3]**:
| COLUMN_0 |
|----------|
| 2 |
[1]: http://sqlfiddle.com/#!3/05979/13
ASKER