|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Passing in Parameters to Stored Procedure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Passing in Parameters to Stored Procedure
|
|
|
| Hey Everyone,
I have a stored procedure called sp_get that has @Comment_Type int
I would like to pass in the value 3 to this stored procedure, so that
@Comment_Type = 3
I have hardcoded the value 3 in the CreateParameter Method.
This is my line of code in my ASP search page:
sp_get.Parameters.Append sp_get.CreateParameter("@Comment_Type", 16,
1, ,3)
My stored procedure:
CREATE PROCEDURE dbo.sp_get_ticket_list
@Analyst_User_ID varchar(8000) @Comment_Type
int
BEGIN
CREATE TABLE #Tickets_By_Comment_User(Ticke
t_ID int null) --ADDED FOR
TICKET COMMENTS
IF @Analyst_User_ID = null
BEGIN
INSERT #Tickets_By_Comment_User (Ticket_ID)
SELECT DISTINCT Ticket_ID
FROM TICKET_HEADER
INSERT INTO
#Tickets_By_Comment_User(Ticke
t_ID)VALUES(null)
END
ELSE
BEGIN
INSERT #Tickets_By_Comment_User (Ticket_ID)
SELECT DISTINCT Ticket_ID
FROM TICKET_COMMENT_DETAIL
WHERE Comment_Type_ID = @Comment_Type
AND User_ID = @Analyst_User_ID
INSERT INTO #Tickets_By_Comment_User(Ticke
t_ID)VALUES(null)
END
SELECT *
FROM Ticket_List
WHERE Ticket_ID = any (SELECT Ticket_ID FROM #Tickets_By_Comment_User)
Drop table #Tickets_By_Comment_User --ADDED TICKET COMMENTS
END
GO
I am receiving incorrect results from my search page, and I think I am
passing in the paramaters wrong to my stored procedure?
Please help if you can, thanks!
-Ray
| |
| Sumanth Kumar 2002-11-20, 11:23 am |
| Ray,
First of all you seem have 2 input paramters in your stored
procedure.
Check 1
Is your ASP code sending 2 paramters?If so are you sending them in the
order in which they are declared in your stored procedure.
Check2
In create parameter statement you did not specify data type (i.e..
adint)
I hope this shou;d resolve ur problem.
-Sumanth
| |
|
| Thanks for your response Sumanth.
Actually, the data type is being specified, but its being specified as
the value instead of the constant. At least I hope that this can work
this way too.
sp_get.Parameters.Append sp_get.CreateParameter("@Comment_Type", 16,
1, ,3)
The 16 acutally really mean adTinyInt.
I found out about this here:
http://www.w3schools.com/ado/met_co...teparameter.asp
-Ray
kumar_sumanth@hotmail.com (Sumanth Kumar) wrote in message news:<f62ae0f8.0211200910.706462a1@posting.google.com>...
> Ray,
>
> First of all you seem have 2 input paramters in your stored
> procedure.
> Check 1
> Is your ASP code sending 2 paramters?If so are you sending them in the
> order in which they are declared in your stored procedure.
>
> Check2
> In create parameter statement you did not specify data type (i.e..
> adint)
>
> I hope this shou;d resolve ur problem.
>
> -Sumanth
|
|
|
|
|