Using RETURN or SELECT values from EXEC
-
How can one use values from an EXEC (or sp_executeSQL) that is known to be a SELECT (or, if needed, RETURN) query? For a simplified example, this works:
SELECT id FROM bugs WHERE project_id IN (
SELECT project
FROM project_user
WHERE user = 144
AND permission_level > 0)
OR 1 IN (
declare @t int
set @t = 0
declare @U char
set @U = 'F'
set @U = (select isnull(support, 'F') FROM personnelData WHERE empID = $empid)
if (@u = 'T')
begin
set @t = 1
end
else
begin
set @t = 0
end
select @t)
...but this does not work
SELECT id FROM bugs WHERE project_id IN (
SELECT project
FROM project_user
WHERE user = 144
AND permission_level > 0)
OR 1 IN (
EXEC 'declare @t int
set @t = 0
declare @U char
set @U = 'F'
set @U = (select isnull(support, 'F') FROM personnelData WHERE empID = $empid)
if (@u = 'T')
begin
set @t = 1
end
else
begin
set @t = 0
end
select @t'))
OK, this example was not *that* simplified (it was copied more or less out of what I am working on), but I hope that it conveys the idea of what I'm trying to do (but not why, of course).
-
You just can't do that.
OR 1 IN (
declare @t int
set @t = 0
declare @U char
set @U = 'F'
set @U = (select isnull(support, 'F') FROM personnelData WHERE empID = $empid)
If you really need another complexe logic, you could call a table function that would return the desired results. But I'm not sure you need something so complexe in this case.
What are you trying to achieve exactly?
-
set your variable before your select statement. Declare t int
Execute sp_somesp t --the variable t will contain an int from the sp
or Execute T=sp_somesp --t will contain the value returned by the sp, note a sp can only return an int data type.
HTH mike
-
What I'm after is to use query text that is already stored in the database, not to exec a stored procedure. (I'm doing this as part of an implementation of role-based permissions in an issue tracker I am trying to refactor.) The idea is that the query text from the database will select (or return, if need be) 1 if a particular user should be granted reporter access for a project or 0 if that user should not. In my example, I forgot to replace '$empid' with 144 the second time it appears, but using REPLACE in T-SQL or a C# equivalent solves that nicely.
I have tried to code this functionality also as both a stored procedure and a stored function and just ran into the same problem because I still need the EXEC. Having the stored statement RETURN 1 rather than SELECT 1 doesn't help either, apparently; I tried for instance:
DECLARE @t INT
EXEC @t = sp_executeSQL @stmt = 'query from database'
RETURN @t
in a stored function but just got an error.
-
Try:
RETURN (@t)
Yes I know, stupid syntax that has tripped me up more than once...
Andy
-
On the stored function side, I tried this:
CREATE FUNCTION rbperm
(@project_id int = 0,
@empid int = 0)
RETURNS INT
AS
BEGIN
declare @rbq nvarchar(1000)
set @rbq = (SELECT pj_role_based_query FROM projects WHERE pj_id = @project_id)
set @rbq = REPLACE(@rbq, '$empid', CAST (@empid AS varchar(5)))
declare @t int
EXEC @t = sp_executeSQL @stmt = @rbq
RETURN (@t)
END
GO
So far so good. Now:
SELECT user.rbperm(3, 144)
GO
yields:
Error: Only functions and extended stored procedures can be executed from within a function.
In case it matters, the SQL that the function call should have retrieved (and then replaced $empid with 144) was:
declare @t int
set @t = 0
if (select isnull(generalTrainingData, 'N') from loginIdent where empID = $empid) like 'R%'
begin
set @t = 1
end
return (@t)
Again, I can change that last statement back to SELECT @t if needed to use a stored procedure approach instead, but I have had no success with that route either.
-
you can't call an sp from a funtion.
You can't delete/update/insert into a user table either (can in table variable)
-
I ended up being away from this project for a while, but I found a workaround for the specific problem I described. However, I ran into a somewhat similar problem later. While researching something else, I discovered the INSERT...EXECUTE syntax, which totally solves this problem (for me, anyway). What is frustrating, though, is that in Microsoft's T-SQL online documentation, this syntax is not mentioned in the help for EXECUTE but only in the help for INSERT. Anyone with any say with Microsoft want to suggest that it be added to the docs for EXEC also? It would have saved me quite a bit of time searching for this answer.
-
It's not obvious but it's there :
in insert / insert (description)
sorry for the french version but I don't ahve the books in english :
INSERT [ INTO]
{ table_name WITH (
[ ...n ] )
| view_name
| rowset_function_limited
}
{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
and 150 pages later in the text
: G. Chargement de données à l'aide des options SELECT et EXECUTE
Cet exemple illustre trois méthodes d'obtention des données d'une table et leur chargement dans une autre. Chaque méthode est basée sur une instruction SELECT multitable incluant une expression et une valeur littérale dans la liste des colonnes.
La première instruction INSERT utilise une instruction SELECT pour extraire directement les données de la table source (authors) et stocker le jeu de résultats dans la table author_sales. La seconde instruction INSERT exécute une procédure qui comprend l'instruction SELECT, et la troisième instruction INSERT exécute l'instruction SELECT comme une chaîne littérale.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'author_sales')
DROP TABLE author_sales
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'get_author_sales' AND type = 'P')
DROP PROCEDURE get_author_sales
GO
USE pubs
CREATE TABLE author_sales
( data_source varchar(20),
au_id varchar(11),
au_lname varchar(40),
sales_dollars smallmoney
)
GO
CREATE PROCEDURE get_author_sales
AS
SELECT 'PROCEDURE', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like '8%'
GROUP BY authors.au_id, authors.au_lname
GO
--INSERT...SELECT example
USE pubs
INSERT author_sales
SELECT 'SELECT', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id LIKE '8%'
GROUP BY authors.au_id, authors.au_lname
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
--INSERT...EXECUTE('string') example
INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like ''8%''
GROUP BY authors.au_id, authors.au_lname
')
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply