SQL Server Performance Forum – Threads Archive
To return value ‘0’ if the fields value are ‘null’
I have a Stored Procedure that selects some fields value from a table and it returns the data retrieved to the application, in order to refill a form page. But now I want to change this SP so that if three specific fields (User_Type, City_num and Activity_num) are ‘null’ it returns ‘0’ value to the application. How can I do it? The SP now:USE market1
GO
ALTER PROC refill_Form2
@User_id bigint, @User_Name varchar(100) output, @User_Type smallint output, @City_num int output, @Address varchar(100) output, @Activity_num smallint output AS SELECT @User_Name = User_Name, @User_Type = User_Type,
@City_num = City_num, @Address = Address, @Activity_num = Activity_num FROM Users
WHERE User_id = @User_id GO Thank you,
Cesar
I dont know if once into your table fields can be null, so I’ll put it to cover as if that were possible USE market1
GO
ALTER PROC refill_Form2
@User_id bigint, @User_Name varchar(100) output, @User_Type smallint output, @City_num int output, @Address varchar(100) output, @Activity_num smallint output AS
set @user_name = ‘0’
set @user_type = 0
set @city_num = 0
set @address = ‘0’
set @activity_num = 0
–above will put values in if there are no records in the users table that match the @user_id
–the isnull(s) below will make sure that if there is no values for a specific field, it will return ‘0’
SELECT @User_Name = isnull(User_Name,’0′), @User_Type = isnull(User_Type,0),
@City_num = isnull(City_num,0), @Address = isnull(Address,’0′), @Activity_num = isnull(Activity_num,0) FROM Users
WHERE User_id = @User_id
Ok thank you, I am going to test your SP tomorrow
Your code works fine
‘ />]<br /><br />Thank you ChrisFretwell
]]>