CODEWITHSUNDEEP
sql-serversql-server-2012
Leonardo Wildt
Leonardo Wildt

Reputation: 2599

Determine if T-SQL select statement returns any rows

I am using Microsoft SQL Server 2012. I want to set a boolean value in a stored procedure. I want to set the value to true if a certain select query returns more than zero rows. 

Declare @ShowQuotingTool as bit 


(select count(*) from tblAgentsLicensedState where AgentCode = '016893' and StateCode in ('AZ','GA','IA', 'TN', 'SC', 'KS', 'MI' ,'NC', 'UT')) > 0;

How do I set @ShowQuotingTool to true or false based on the select query? I get the error Incorrect syntax near ">".

Upvotes: 1

Views: 94

Answers (2)

ughai
ughai

Reputation: 9890

You can just assign @ShowQuotingTool to the count query. This is based on the fact that any count > 0 will set the bit variable @ShowQuotingTool to true and count = 0 will set it to false

Declare @ShowQuotingTool as bit 

SELECT @ShowQuotingTool = (select count(*) from tblAgentsLicensedState where AgentCode = '016893' and StateCode in ('AZ','GA','IA', 'TN', 'SC', 'KS', 'MI' ,'NC', 'UT'))

You should preferably use EXISTS which would have better performance than COUNT query above.

IF EXISTS(select * from tblAgentsLicensedState where AgentCode = '016893' and StateCode in ('AZ','GA','IA', 'TN', 'SC', 'KS', 'MI' ,'NC', 'UT'))
    SET @ShowQuotingTool = 1
ELSE 
    SET @ShowQuotingTool = 0

Upvotes: 3

Ronen Ariely
Ronen Ariely

Reputation: 2434

Good Day,

  1. Please post DDL+DML if this is not what you are looking for. In any case please remember next time to post DDL+DML (queries to create the relevant tables and to insert some sample data, in order to give us the tools to reproduce the issue).

  2. Back to your question:

If you have an SP that return the result of select query (or any DML query), that mean that you need this query to execute as it is. The idea of using COUNT function on the results mean that you execute another query, which is not logic action to do, since we already have the information in the headers of the result.

If you notice, by default, every time that you execute a query in the SSMS for example you get the information regarding number of rows: "(x row(s) affected)". There is no need to use another query.

If you are using external application that you develop, and your SP do not use "SET NOCOUNT ON", then this information coming back from the server :-)

You can see examples here: https://msdn.microsoft.com/en-us/library/ms171921.aspx?f=255&MSPPError=-2147217396

Upvotes: 1

Related Questions