question

John 3 1 avatar image
John 3 1 asked

Stored Procedures using variables from a user table

I have list of unique users in a User Table. I need to run a stored procedure for each of those users. What is the best way to achieve this? I also need to have an added WHERE clause for the User Table (WHERE Active = 1). Thanks.

stored-procedures
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image Tom Staab ♦ commented ·
John, I updated my answer based on your comment. I changed it to use your object names. The error with the @sql variable is because you aren't using SQL Server 2008. I fixed that too.
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
Can you paste the stored procedure here? After seeing your response to Jeff, I think we might be able to help you redesign this to be much more efficient (i.e. use 1 procedure call for the whole process).
0 Likes 0 ·
John 3 1 avatar image John 3 1 commented ·
Thank you all for your responses. You guys are wonderful.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

Taking CirqueDeSQLeil's idea one step further ...

table variable for testing ...

DECLARE @users_table TABLE (user_id int, user_name varchar(100), active bit);
INSERT INTO @users_table
    (user_id, user_name, active)
    SELECT 1, 'Amy', 1
    UNION
    SELECT 2, 'Bob', 0
    UNION
    SELECT 3, 'Mary', 0
    UNION
    SELECT 4, 'Steve', 1

Here's the code you need:

DECLARE @sql nvarchar(max); SET @sql = N'';
DECLARE @proc_name nvarchar(100); SET @proc_name = N's25Percentage';

-- select user ID values
SELECT @sql = @sql + N'EXEC ' + @proc_name + N' ' + CAST(ut.userid AS nvarchar(4)) + N'; '
FROM users ut
WHERE ut.active = 1;

PRINT @sql;
EXEC sp_executesql @sql;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
select 'exec procname ' + userid + ' ' from userstable where active = 1

Very basic query that will return a list of commands that you can execute by copying to a new query window. The query could be considerably more complex depending on the requirements. Using what you gave, this query should suffice.

6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

John 3 1 avatar image John 3 1 commented ·
not quite... It just returned a list of concatenated sp name with each of the userids. How can I actually use each of those usernames as a variable in my sp?
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
You need a space inside the quotes after "procname" -- select 'exec procname '
0 Likes 0 ·
CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Thanks Tom for spotting that.
0 Likes 0 ·
John 3 1 avatar image John 3 1 commented ·
Still getting a list of sp name plus each of the userids. I was looking for a way to run this sp for each user.. not just a list. eg: EXEC sp1 for user1 from UserTable (if UserTable.Active = 1).... then same logic for user2 - EXEC sp1 for user2 from UserTable (if UserTable.Active = 1)... and so on for all the users.
0 Likes 0 ·
CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
The query is only designed to return a list of commands that you must copy and execute.
0 Likes 0 ·
Show more comments
Jeff Moden avatar image
Jeff Moden answered

Ummm... this is guaranteed RBAR. What does the stored procedure that you want to run for each user actually do? I ask because there may be a better way.

5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

John 3 1 avatar image John 3 1 commented ·
I was thinking of cursors, but not quite sure if and how to make it work. (i know everybody hates cursors) The objective here is: I want to append the top 25% accounts for each user to a new table. Currently I have it setup on a stored procedure with the UserID being passed thru as a variable. But I have to run the stored procedure manually to get that 25% for each user. I was wondering if i could automate that process so I don't have to manually enter the userID variable.
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
+1 Great point, Jeff. I was so focused on just answering the question, I didn't take the time to consider whether or not it's really the right approach.
0 Likes 0 ·
John 3 1 avatar image John 3 1 commented ·
Thanks Jeff. I'm pretty sure there is a better way of doing this, but for now it works. I'll revisit this on a later phase.
0 Likes 0 ·
Jeff Moden avatar image Jeff Moden commented ·
Sorry for the delay, John. I've been pretty busy. I'm sure there's a better way. You just need to post a bit more information like the CREATE TABLE statement (including indexes, constraints, triggers, etc) and perhaps some test data for folks to test the coded answers on. Take a look at the following link for how to do that. http://www.sqlservercentral.com/articles/Best+Practices/61537/
0 Likes 0 ·
Jeff Moden avatar image Jeff Moden commented ·
@Tob Staab. Thanks for the legup and the comment, Tom.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.