May 23, 2013 at 2:54 am
Morning folks,
I'm a bit stumped here. The issue I've got is syntax related I'm sure.
Essentially I want to run the results of an Stored Proc into a variable
The error that I'm getting is:
Incorrect syntax near the keyword 'EXEC'
DECLARE @Cols as nvarchar(max)
SET @Cols = 'SELECT @ColsOut = EXEC DB..sp_1 ''DB..Tmp_LkUp_Tbl'' , NULL , B, ''MOP'',''TTL_Cnt'' '
EXEC sp_Executesql @Cols, N'@ColsOut varchar(max) Out', @cols OUTPUT
PRINT @Cols
Any help would be greatly appreciated as this is starting to frustrate me somewhat
Thanks
May 23, 2013 at 3:05 am
the syntax
EXEC @myvariable = dbo.myproc;
will take the RETURN value from the stored proc and put it into @myvariable, which IIRC must be of Integer data type.
It looks like you are trying to return a String (a list of column names maybe?), which you can do two ways (ok more but 2 for now):
You can put your output string into an OUTPUT parameter of the stored procedure and do this:
CREATE PROC dbo.myproc @result varchar(max) OUTPUT
AS
SET @result='myresult';
RETURN;
GO
DECLARE @myvariable varchar(max);
EXEC dbo.myproc @myvariable OUTPUT;
GO
OR you can SELECT your string and insert the results of the stored proc into a table :
CREATE PROC dbo.myproc
AS
SELECT 'myresult' as Col1;
GO
CREATE TABLE #result ( myresult VARCHAR( MAX ) );
GO
INSERT #result ( myresult )
EXEC dbo.myproc;
GO
Perhaps if you post the code for sp_1 we can give you a more specific answer?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 23, 2013 at 3:09 am
DECLARE @OutPara
EXEC @OutPara = dbo.Proc Parameter
May 23, 2013 at 3:13 am
HI there
Just going to start digesting your response, but just for completeness here is SP1 (In reality called sp_RemFlds....)
It's designed to be flexible to return differing outputs depending on what I need, in this case I just want it to return the all field names from the given Tbl (with an alias) without the fields in the final 2 vars.
I will then use this list in another statement to just pull the fields I need
[Code]
ALTER PROCEDURE sp_RemFldsFromSelect @Tbl as nvarchar(600), @ResCol as varchar(3) = NULL, @Alias as varchar(10) = NULL, @Cl1 as nvarchar(100) = NULL, @Cl2 as nvarchar(100) = NULL, @Cl3 as nvarchar(100) = NULL, @Cl4 as nvarchar(100) = NULL, @Cl5 as nvarchar(100) = NULL
as
/*
This Sp will allow you to query a table and returen a result that excludes some columns
@Tbl= Will need to be a fully extended name as you'll be running this invariably from a different DB
@ResCol= Res = Return Results of the query, Col = Return the columns only
Examples:
EXEC DB..sp_RemFldsFromSelect '[DB]..Tbl' returns all columns
EXEC DB..sp_RemFldsFromSelect '[DB]..Tbl' , NULL ,'B','Syr' returns all columns apart from Syr with an alias of B(ie B.SysCustRef)
EXEC DB..sp_RemFldsFromSelect '[DB]..Tbl' , 'Col' ,'Syr' returns all columns apart from Syr
EXEC DB..sp_RemFldsFromSelect '[DB]..Tbl' , 'Res' ,'Syr' returns a select * for all columns apart from Syr
*/
DECLARE
@colsnvarchar(max)
, @queryvarchar(max)
, @SCvarchar(128)
, @TblOvarchar(128)
SET@Cl1 = CASE WHEN @Cl1 IS NOT NULL THEN '''' + @Cl1 + '''' ELSE '''NULL'''END
+ CASE WHEN @Cl2 IS NOT NULL THEN ',''' + @Cl2 + '''' ELSE ''END
+ CASE WHEN @Cl3 IS NOT NULL THEN ',''' + @Cl3 + '''' ELSE ''END
+ CASE WHEN @Cl4 IS NOT NULL THEN ',''' + @Cl4 + '''' ELSE ''END
+ CASE WHEN @Cl5 IS NOT NULL THEN ',''' + @Cl5 + '''' ELSE ''END
SET @SC= CASE WHEN PARSENAME(@Tbl, 3) IS NULL THEN 'INFORMATION_SCHEMA.COLUMNS' ELSE PARSENAME(@Tbl, 3) + '.INFORMATION_SCHEMA.COLUMNS' END
SET @TblO= PARSENAME(@Tbl, 1)
SET @ResCol = CASE WHEN @ResCol IS NULL THEN 'Col'ELSE @ResColEND
SET @Alias= CASE WHEN @Alias IS NULL THEN ''ELSE @Alias + '.'END
SELECT @Cols =
'SELECT @ColsOut =
STUFF
(
(SELECT * FROM
(
SELECT TOP 100 PERCENT ''], [''+''' + @Alias +''' + COLUMN_NAME AS [text()]
FROM
(
SELECT COLUMN_NAME, ORDINAL_POSITION as Ord
FROM ' + @SC + '
WHERE TABLE_NAME = (''' + @TblO + ''')
AND COLUMN_NAME NOT IN (' + @Cl1 + ')
) as R2
ORDER BY Ord
) as R1
FOR XML PATH('''')
), 1, 2, ''''
)+ '']''
'
PRINT @Cols
EXEC sp_Executesql @Cols, N'@colsOut varchar(max) Out', @cols OUTPUT
PRINT @Cols
SELECT @Query = CASE WHEN @ResCol = 'Col'
THEN 'select ''' + @cols + ''' As Cols'
ELSE 'select ' + @cols + ' from ' + @Tbl + ''
END
PRINT @Query
exec (@Query)
[/code]
May 23, 2013 at 3:17 am
Cheers Bhaskar.Shetty
I knew it would be a simple explanation
And a big thanks to Mister.Magoo too 😀
Final solution in case anyone stumbles across this (or wants a dynamic solution to the same issue)
The actual reason I need this is the data I need to query on a regular basis is made up of a pivot, where the actual columns alter ( names & amount of columns returned)
Obviously the task I employ has an FK built in, but I dont want it to show in the final query; along with a generic Total column.
So in this scenario I have my pivot results in a table (DB..Tbl), now I just want to join selected fields from that back to the main results table (MainResultsTbl)
Main SP to do the donkey work
ALTER PROCEDURE sp_RemFldsFromSelect @Tbl as nvarchar(600), @QueryR varchar(max) OUTPUT, @ResCol as varchar(3) = NULL, @Alias as varchar(10) = NULL, @Cl1 as nvarchar(100) = NULL, @Cl2 as nvarchar(100) = NULL, @Cl3 as nvarchar(100) = NULL, @Cl4 as nvarchar(100) = NULL, @Cl5 as nvarchar(100) = NULL
as
/*
This Sp will allow you to query a table and returen a result that excludes some columns
@Tbl= Will need to be a fully extended name as you'll be running this invariably from a different DB
@ResCol= Res = Return Results of the query, Col = Return the columns only
Examples:
EXEC DB..sp_RemFldsFromSelect DB1..Tbl', @Cols OUTPUT :returns all column names to the var @Cols in the 'calling' process
EXEC DB..sp_RemFldsFromSelect 'DB1..Tbl', @Cols OUTPUT , NULL ,'B','Syr' :returns all columns apart from Syr with an alias of B(ie B.TblID) to local var @out
EXEC DB..sp_RemFldsFromSelect 'DB..Tbl', @Cols OUTPUT, Col ,B, 'MOP_SysCustRef','TTL_Cnt' :returns all column names apart from MOP_SYSCustRef and TTL_CNt
EXEC DB..sp_RemFldsFromSelect '[DB]..Tbl', @Cols OUTPUT , 'Res' ,'Syr' :returns a select statement for all columns apart from Syr
*/
DECLARE
@colsnvarchar(max)
, @SCvarchar(128)
, @TblOvarchar(128)
SET@Cl1 = CASE WHEN @Cl1 IS NOT NULLTHEN '''' + @Cl1 + ''''ELSE '''NULL'''END
+ CASE WHEN @Cl2 IS NOT NULLTHEN ',''' + @Cl2 + ''''ELSE ''END
+ CASE WHEN @Cl3 IS NOT NULLTHEN ',''' + @Cl3 + ''''ELSE ''END
+ CASE WHEN @Cl4 IS NOT NULLTHEN ',''' + @Cl4 + ''''ELSE ''END
+ CASE WHEN @Cl5 IS NOT NULLTHEN ',''' + @Cl5 + ''''ELSE ''END
SET @SC= CASE WHEN PARSENAME(@Tbl, 3) IS NULLTHEN 'INFORMATION_SCHEMA.COLUMNS'ELSE PARSENAME(@Tbl, 3) + '.INFORMATION_SCHEMA.COLUMNS' END
SET @TblO= PARSENAME(@Tbl, 1)
SET @ResCol = CASE WHEN @ResCol IS NULLTHEN 'Col'ELSE @ResColEND
SET @Alias= CASE WHEN @Alias IS NULL OR @ResCol <> 'Col' THEN ''ELSE @Alias + '.'END
SELECT @Cols =
'SELECT @ColsOut =
STUFF
(
(SELECT * FROM
(
SELECT TOP 100 PERCENT ''], ' + @Alias +'['' + COLUMN_NAME AS [text()]
FROM
(
SELECT COLUMN_NAME, ORDINAL_POSITION as Ord
FROM ' + @SC + '
WHERE TABLE_NAME = (''' + @TblO + ''')
AND COLUMN_NAME NOT IN (' + @Cl1 + ')
) as R2
ORDER BY Ord
) as R1
FOR XML PATH('''')
), 1, 2, ''''
)+ '']''
'
--PRINT @Cols
EXEC sp_Executesql @Cols, N'@colsOut varchar(max) Out', @cols OUTPUT
--PRINT @Cols
SELECT @QueryR =
CASEWHEN @ResCol = 'Col'
THEN ''+ @cols+''
ELSE 'select ' + @cols + ' from ' + @Tbl + ''
END
RETURN
--PRINT @QueryR
Actual usage in parent procedure
DECLARE @sql as nvarchar(max), @Cols as varchar(max)
--Now get the column list minus the fields we dont need
EXEC DB..sp_RemFldsFromSelect 'DB1..Tbl', @Cols OUTPUT, Col ,B, 'MOP_Ref','TTL_Cnt'
SET @sql =
'SELECT T.*,'+ @Cols + '
FROM MainResultsTbl as T
INNER JOIN Db1..Tbl as R
ON R.ID = T.ID
'
--PRINT @sql
EXEC (@SQL)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply