February 23, 2006 at 9:31 am
How do I return the entire single record from a stored procedure? Do I have do declare variables for each field in the table, or is there an easier way? Can someone please provide me with an example?
February 23, 2006 at 9:33 am
create procedure test
as
select * from sysdatabases where dbid = 1
go
exec test
February 23, 2006 at 10:24 am
Here is my stored procedure:
CREATE PROCEDURE dbo.GET_USER_DETAIL (@lan_id varchar)
AS
SELECT * FROM APP_USER WHERE [LAN_ID]=@lan_id
GO
When I exec the stored proc, nothing gets returned, but when I run the select statement on it's own, I get the proper results. Any clue why?
February 23, 2006 at 10:31 am
What is the table definition of APP_USER? How are you calling the procecedure?
The create statement should be something like this:
CREATE PROCEDURE dbo.GET_USER_DETAIL (@lan_id varchar(255)) where 255 is the same size as the declaration for the LAN_ID column.
February 23, 2006 at 10:41 am
Adding the length of the field did the trick. I don't understand why that makes a difference, tho. Can it only compare fields that defined as being the same size?
February 23, 2006 at 10:46 am
You can compare fields of any length but without the size, SQL doesn't know how big the parameter should be.
Try adding this and see what you get:
CREATE PROCEDURE dbo.GET_USER_DETAIL (@lan_id varchar)
AS
print '<' + @lan_id + '>'
GO
exec GET_USER_DETAIL 'jeff'
February 23, 2006 at 2:41 pm
If you omit the length of varchar, sql server picks the default length.
Normally it is around 30.
So CREATE PROCEDURE dbo.GET_USER_DETAIL (@lan_id varchar)
is actually like
CREATE PROCEDURE dbo.GET_USER_DETAIL (@lan_id varchar(30))
It bites
February 24, 2006 at 8:49 am
Actually, in this case the default size is 1.
CREATE PROCEDURE dbo.GET_USER_DETAIL (@lan_id varchar)
AS
print '<' + @lan_id + '>'
GO
exec GET_USER_DETAIL 'jeff'
The output is <j>
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply