CODEWITHSUNDEEP
sqlsql-server
GeoVIP
GeoVIP

Reputation: 1564

How to add to stored procedure call selected vallue

Hi guys have select in stored procedure (myProc) like this :

SELECT @body = 'User: ' + @admin_username  + ' set Admin role TO: ' + [username] 
FROM [dbo].[users]
WHERE [id] = @user_id;

and want add @body in another stored procedure call which is in current procedure

exec sendMail '','',@body

How do it ? @body type is nvarchar(max) I think i have wrong select because when i exec my procedure have an error : cannot insert the null value

Upvotes: 0

Views: 54

Answers (2)

Mentatmatt
Mentatmatt

Reputation: 525

If the username column is null for a given user_id then @body will return NULL. Use ISNULL([username],'') to at least return an empty string for the username.

Also try using an OUTPUT parameter to return @body to the next stored procedure. See: http://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

Upvotes: 0

Schalk
Schalk

Reputation: 142

Make sure that your @Admin_Username and [Username] doesn't return a null, otherwise your whole @body variable will end up with a NULL.

Try something like this:

SELECT @body = 'User: ' + ISNULL(@admin_username, '')  + ' set Admin role TO: ' + ISNULL([username], '')
FROM [dbo].[users]
WHERE [id] = @user_id;


exec sendMail '','',@body

Upvotes: 1

Related Questions