putting a dynamic query in Stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

putting a dynamic query in Stored procedure?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-08-28 : 15:59:08

Hi friends,

I want to put this query in stored procedure so that I can exec the sp on a telerik report by calling it. How can achieve it

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
DECLARE @SpatialId AS NVARCHAR(MAX);
DECLARE @SpatialIdDynamic AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
QUOTENAME(AnalysisDescription)
FROM GISportal.vwSpatialReport
where Convert(uniqueidentifier, '54CB783F-2207-45BE-A617-E730AD7D21C5') = SpatialReportID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');

SELECT @query =

'SELECT *
FROM
(
SELECT
SpatialReportID,
AnalysisDescription,
ResultDescription
FROM GISportal.vwSpatialReport
WHERE Convert(uniqueidentifier,'+@SpatialIdDynamic +') = SpatialReportID

) AS t
PIVOT
(
MAX(ResultDescription)
FOR AnalysisDescription IN( ' + @cols + ' )' +
' ) AS p ; ';

execute(@query);

thank you very much

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-28 : 16:07:39

Assuming the query as it exists is working correctly for you,

CREATE PROCEDURE dbo.TestProc

	@cols AS NVARCHAR(MAX),
	@query AS NVARCHAR(MAX),
	@SpatialId AS NVARCHAR(MAX),
	@SpatialIdDynamic AS NVARCHAR(MAX)

AS
select @cols = STUFF((SELECT distinct ',' +
.....

If you don't want those parameters on the stored proc, it is even simpler:


CREATE PROCEDURE dbo.TestProc

as

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
DECLARE @SpatialId AS NVARCHAR(MAX);
DECLARE @SpatialIdDynamic AS NVARCHAR(MAX);
.....

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-29 : 05:01:54

As far as I understood this may work fine for you...

CREATE PROCEDURE dbo.USP_TestProcedure

@SpatialIdDynamic AS NVARCHAR(MAX)

AS
SET NOCOUNT ON
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
DECLARE @SpatialId AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AnalysisDescription)
FROM GISportal.vwSpatialReport
where Convert(uniqueidentifier, '54CB783F-2207-45BE-A617-E730AD7D21C5') = SpatialReportID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '')

SELECT @query =
'SELECT * FROM
(
SELECT
SpatialReportID,
AnalysisDescription,
ResultDescription
FROM GISportal.vwSpatialReport
WHERE Convert(uniqueidentifier,'+@SpatialIdDynamic +') = SpatialReportID

) AS t
PIVOT
(
MAX(ResultDescription) FOR AnalysisDescription IN( ' + @cols + ' )' +' ) AS p ; ';

EXECUTE (@query)

P.Kameswara rao

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-08-30 : 10:56:27

Thank you my friends and speciallly kamesh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources