Help with Stored Procedure and passing varible
-
Hello, SQL 2005 I have a stored procedure and I am not sure I wrote it correctly to except a parameter of ticketid. In VS I can see the fields however no data gets passed. When I select query builder from VS I get an error "unable to parse query text" I then exceute query and the fields show up with no data. If I set the Parameter @ticketid to a value it works in SQL Query Analyser. In the command and editor window in VS-? under parameters I have ticketid value request.querystring ("@ticketid") Query String Field = @ticketid here is the stored procedure: Declare @ticketid as varchar (20) Declare @Technician as varchar (20) set @Technician=(SELECT email FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id = job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20))) SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician 'Technician Assigned',job_ticket.subject 'Issue Description', report_date 'Open Date',tech_note.note_text 'Work Notes' FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id = job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) Am I not passing the varible correctly or is the stored procedure incorrect? Any help is greatly appreciated; I have been stuck on this for days. Thanks
Regards, Hulicat
-
Hello, SQL 2005 I have a stored procedure and I am not sure I wrote it correctly to except a parameter of ticketid. In VS I can see the fields however no data gets passed. When I select query builder from VS I get an error "unable to parse query text" I then exceute query and the fields show up with no data. If I set the Parameter @ticketid to a value it works in SQL Query Analyser. In the command and editor window in VS-? under parameters I have ticketid value request.querystring ("@ticketid") Query String Field = @ticketid here is the stored procedure: Declare @ticketid as varchar (20) Declare @Technician as varchar (20) set @Technician=(SELECT email FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id = job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20))) SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician 'Technician Assigned',job_ticket.subject 'Issue Description', report_date 'Open Date',tech_note.note_text 'Work Notes' FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id = job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) Am I not passing the varible correctly or is the stored procedure incorrect? Any help is greatly appreciated; I have been stuck on this for days. Thanks
Regards, Hulicat
okay so it needed to be created as...... question what does this do as oppsoed to how I had it: CREATE PROCEDURE dbo.sp_dticket ( @ticketid as varchar(20) ) AS CREATE PROCEDURE dbo.sp_dticket ( @ticketid as varchar(20) ) AS Declare @Technician as varchar (20) set @Technician=(SELECT email FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id = job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20))) SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician 'Technician Assigned',job_ticket.subject 'Issue Description', report_date 'Open Date',tech_note.note_text 'Work Notes' FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id = job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) GO
Regards, Hulicat
-
Hello, SQL 2005 I have a stored procedure and I am not sure I wrote it correctly to except a parameter of ticketid. In VS I can see the fields however no data gets passed. When I select query builder from VS I get an error "unable to parse query text" I then exceute query and the fields show up with no data. If I set the Parameter @ticketid to a value it works in SQL Query Analyser. In the command and editor window in VS-? under parameters I have ticketid value request.querystring ("@ticketid") Query String Field = @ticketid here is the stored procedure: Declare @ticketid as varchar (20) Declare @Technician as varchar (20) set @Technician=(SELECT email FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id = job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20))) SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician 'Technician Assigned',job_ticket.subject 'Issue Description', report_date 'Open Date',tech_note.note_text 'Work Notes' FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id = job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) Am I not passing the varible correctly or is the stored procedure incorrect? Any help is greatly appreciated; I have been stuck on this for days. Thanks
Regards, Hulicat
Dear i it is not necassry to CAST the value if u have int type Value. Query gives the result. if u send me Table structure thean i will solve you clearliry. Thanks and Have a Nice day
-
Dear i it is not necassry to CAST the value if u have int type Value. Query gives the result. if u send me Table structure thean i will solve you clearliry. Thanks and Have a Nice day
It kept throwing an error unable to convert varchar to int...even when it was declared as int....so I dropped in the cast and it worked. I was just wonding what is different about putting the varible in brackets before the "AS"? It's working correctly; I would just like to better undersrand how Thanks,
Regards, Hulicat