SQL Server – Parentheses Use in SET Statement

selectsql servert-sql

I am trying to compute the number of distinct pairs sender_id, send_to_id and assign it to a variable @UniqueRequests. When I try the statmement below, I get an error saying the syntax near ) is incorrect.

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request));

So my questions would be

What is the correct statement?
It seems that we must use parentheses around a SELECT when assigninig a variable. That is to say, we need to write SET @MyVariable = (SELECT some_expression); Why is that so?
Is there any documentation page on the use of parentheses in SQL Server in general?

Thank you for your help!

Best Answer

Actually this error is a bit confusing and that's the reason you are asking these questions.

Correct statement would be:

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request) friends);

I did testing on some other table with same concept and you may see the result as below:

Answer to these questions are as below:

What is the correct statement?

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request) friends);

It seems that we must use parentheses around a SELECT when assigninig a variable. That is to say, we need to write SET @MyVariable = (SELECT some_expression); Why is that so?

This is because you are setting the value, and you can't assign keyword and other strings to an int, rather you want to assign the value which means query has to run first and that result you want to assign to this particular integer variable.

Alternatively if you wish to only set the value, you can use below:

DECLARE @UniqueRequests int;
select @UniqueRequests = COUNT(*) FROM CommandLog
print @UniqueRequests

Is there any documentation page on the use of parentheses in SQL Server in general?

You may refer this from Micrososoft, this thread and this link.

Hope this helps.

Related Solutions

Sql-server – use parameters in a Cte

The first error is on Line 5, telling you that you cannot use Functions in your parameter's default values.

Pass in NULL as the default value, then sort the parameters out inside the Stored Procedure:

CREATE PROCEDURE dbo.Test
(
  @StartDate As datetime = NULL 
  ,@EndDate As datetime = NULL 
)
AS

BEGIN

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -8, CAST(GETDATE() AS DATE))
IF @StartDate IS NULL
  SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0);

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -1, @StartDate)
IF @EndDate IS NULL
  SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0);


END

Once you have fixed this issue, and capitalise the @startDate parameter to read @StartDate, you should be fine.

For reference, there's an open Connect Item (currently 7 years old) that is Microsoft acknowledging it's on their 'ToDo' list.

Sql-server – SQL Server passing variable to a string in a stored proc

The parameters supplied to a procedure must be constants or variables. A function or the output of a function or an expression cannot be used.

You would have to first set a variable with the required expression:

declare @investigation varchar(100);
set @investigation = 'Investigation is pending for  ['+ @investigationidout +'] ';

EXEC sp_wf_create_notification 
    @processid,
    @vactivityid,
    @vstepid,
    1,
    @vidColumn,
    @vidColumnTable,
    @investigationidout,
    @owner,
    'Pending Investigation',
    @investigation
    @nextstepurl,
    @vstatus OUTPUT,
    @verror OUTPUT
;

For OUTPUT variables, don't forget to add OUTPUT after its name.

See MSDN Specify Parameters:

The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid.

Best Answer

Related Solutions

Sql-server – use parameters in a Cte

Sql-server – SQL Server passing variable to a string in a stored proc

Related Question