T-SQL Stored Pro Help [modified]
-
I have the following stored procedure that works except that the "print" does display on the page when the "IF" condition is met...it shows up as a message when run from Query analyzer but not when the stored procedure is called. Can anyone tell me what is wrong with my print? I have tried a few things with no success just errors. the solutions I saw in books online did not work for me. here is the stored procedure: ( @ticketid as varchar(20) ) AS Declare @Technician as varchar (40) declare @count int set @count=(Select count(*) from job_ticket WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) and status_type_id like '[1-2]') if @count =0 print 'there are no open tickets' 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, job_ticket.question_text 'Report', 'Ticket Number', @Technician 'Technician Assigned',job_ticket.subject 'Issue Description', report_date 'Open Date' FROM job_ticket WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) and status_type_id like '[1-2]' -- modified at 20:56 Thursday 17th May, 2007
Regards, Hulicat
-
I have the following stored procedure that works except that the "print" does display on the page when the "IF" condition is met...it shows up as a message when run from Query analyzer but not when the stored procedure is called. Can anyone tell me what is wrong with my print? I have tried a few things with no success just errors. the solutions I saw in books online did not work for me. here is the stored procedure: ( @ticketid as varchar(20) ) AS Declare @Technician as varchar (40) declare @count int set @count=(Select count(*) from job_ticket WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) and status_type_id like '[1-2]') if @count =0 print 'there are no open tickets' 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, job_ticket.question_text 'Report', 'Ticket Number', @Technician 'Technician Assigned',job_ticket.subject 'Issue Description', report_date 'Open Date' FROM job_ticket WHERE job_ticket.job_ticket_id =Cast (@ticketid as varchar (20)) and status_type_id like '[1-2]' -- modified at 20:56 Thursday 17th May, 2007
Regards, Hulicat
How are you running your stored procedure? Under ADO, a print statement does not create a resultset. Instead it adds an item to the Connection.Errors array. Normally I would do the zero-records check back on the front-end (because the resultset is empty). Also, most people add "SET NOCOUNT ON" to the top of their SQL-Server stored procedures.
-
How are you running your stored procedure? Under ADO, a print statement does not create a resultset. Instead it adds an item to the Connection.Errors array. Normally I would do the zero-records check back on the front-end (because the resultset is empty). Also, most people add "SET NOCOUNT ON" to the top of their SQL-Server stored procedures.