SQL Server Performance Forum – Threads Archive

Default value of stored procedure

Hi I have a SP as follows: CREATE PROCEDURE SpTest
(
@id AS NVARCHAR(100),
@DDn AS BIT = NULL
)
AS
IF (@DDn = 1)
BEGIN
SELECT ‘abcd’
END
ELSE
BEGIN
SELECT ‘defg’
END
RETURN
GO Questions:
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.

---

]]>