SQL Server Performance Forum – Threads Archive
Default value of stored procedureHi I have a SP as follows: CREATE PROCEDURE SpTest
@id AS NVARCHAR(100),
@DDn AS BIT = NULL
IF (@DDn = 1)
1) is it a correct way to assign a default value in a stored procedure.
2) when i call this stored procedure from .NET code but it doesn’t return any solution but if i change the default value from NULL to 1 then code returns a solution.
If @DDn is not assigned a value when calling the procedure, it gets NULL. The IF statement therefore evaluates as false, and your procedure executes the SELECT ‘defg’ instruction. You will only see ‘defg’ in your calling application if it is expecting a recordset, but I guess your application is looking at the @id parameter, not at a recordset. To return the string through the parameter, use SET @id = ‘defg’ or SELECT @id = ‘defg’. Not sure that you need the RETURN statement.
generally we use return statement when we return or when we want to output declared variable,
but in ur query u r not returning any variable but the select statement u r executing.
so it will return the recordset with value ‘defg’. it’s recommended to declare a bit data type as 0 or 1.