October 22, 2007 at 1:13 am
hi, i think its a pretty simple task but i just cant seem to get it right as I am pretty new to SQL.
What i want to do is search a table given 2 parameters (first name and last name) and return the customer ID of the matching row.
here is what i have now.. which is wrong...
ALTER PROCEDURE [dbo].[usp_GetCustID]
@F_NAME varchar(20),
@L_NAME varchar(20)
As
where (tbl_Customer.firstName = @F_NAME
and tbl_Customer.lastName = @L_NAME)
return tbl_Customer.pkCustomerID
I want it to return the value of the field as an int and not a data set...
October 22, 2007 at 1:36 am
You could either use a user defined function or a procedure with an output parameter.
Two examples:
CREATE FUNCTION dbo.f1 ( )
RETURNS INT
AS BEGIN
DECLARE @a INT
SELECT @a = SUM(a)
FROM dbo.sometable
RETURN @a
END
GO
SELECT dbo.f1()
GO
CREATE PROC dbo.proc1 ( @a INT OUTPUT )
AS
BEGIN
SELECT @a = SUM(a)
FROM dbo.sometable
END
GO
DECLARE @ret INT
EXEC dbo.proc1 @ret OUTPUT
SELECT @ret
You can read more in BOL
http://technet.microsoft.com/en-us/library/ms187926.aspx
http://msdn2.microsoft.com/en-us/library/ms186755.aspx
Regards,
Andras
October 22, 2007 at 6:23 am
insane_professional (10/22/2007)
hi, i think its a pretty simple task but i just cant seem to get it right as I am pretty new to SQL.What i want to do is search a table given 2 parameters (first name and last name) and return the customer ID of the matching row.
here is what i have now.. which is wrong...
ALTER PROCEDURE [dbo].[usp_GetCustID]
@F_NAME varchar(20),
@L_NAME varchar(20)
As
where (tbl_Customer.firstName = @F_NAME
and tbl_Customer.lastName = @L_NAME)
return tbl_Customer.pkCustomerID
I want it to return the value of the field as an int and not a data set...
From what you have posted, there is no completed select statement.
ALTER PROCEDURE [dbo].[usp_GetCustID]
@F_NAME varchar(20),
@L_NAME varchar(20),
@pkCustomerId int OUTPUT
As
SELECT @pkCustomerId = pkCustomerId
FROM tbl_Customer
WHERE (tbl_Customer.firstName = @F_NAME
and tbl_Customer.lastName = @L_NAME)
GO
Call it this way:
DECLARE @Id int
EXEC usp_getCustId @F_Name = 'x', @L_Name= 'y', @pkCustomerId = @Id OUTPUT
SELECT @Id
GO
Remember, in order to select data from a table, you have to go through SELECT... FROM... WHERE. Also, the above will only work if there is one and only one row returned by the select statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2007 at 4:32 am
insane_professional (10/22/2007)
hi, i think its a pretty simple task but i just cant seem to get it right as I am pretty new to SQL.What i want to do is search a table given 2 parameters (first name and last name) and return the customer ID of the matching row.
here is what i have now.. which is wrong...
ALTER PROCEDURE [dbo].[usp_GetCustID]
@F_NAME varchar(20),
@L_NAME varchar(20)
As
where (tbl_Customer.firstName = @F_NAME
and tbl_Customer.lastName = @L_NAME)
return tbl_Customer.pkCustomerID
I want it to return the value of the field as an int and not a data set...
Hi, hope i'm not too late:D
for me i'll just do this, and get the "OUTPUT" at the asp side 😉
ALTER PROCEDURE [dbo].[usp_GetCustID]
@F_NAME varchar(20),
@L_NAME varchar(20),
@CustomerID INT OUTPUT
As
BEGIN
SELECT @CustomerID = tbl_Customer.pkCustomerID
FROM tbl_Customer
WHERE (tbl_Customer.firstName = @F_NAME)
AND (tbl_Customer.lastName = @L_NAME)
END
GO
October 23, 2007 at 4:34 am
Cheers guys, thanks so much for all ur help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply