Stored procedure variable value into select

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 2005 Forums

Transact-SQL (2005)

Stored procedure variable value into select

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-11 : 14:38:19

Can you please help, How can i use SQLSelect variable's field value into the following SP.

ALTER PROCEDURE [dbo].[SP_GetDrawings]
(
		@modid int,
		@modname nvarchar(20),
		@language nvarchar(50)
)
	
 AS

DECLARE @ErrorCode		int
DECLARE @SQLSelect		nvarchar(1000)

SELECT @SQLSelect = 
	CASE 	@language
		WHEN 'English' THEN ' rtrim(isnull(Disp.Englishtext,'''')) as Disposition,'
		WHEN 'Spanish' THEN ' rtrim(isnull(Disp.SpanishText,'''')) as Disposition,'
		ELSE 'rtrim(isnull(Disp.Englishtext,'''')) as Disposition,'
END

SET NOCOUNT ON
SELECT @ErrorCode = @@Error
IF @ErrorCode = 0
BEGIN
	SELECT  d.drwid,
		isnull(rtrim(dmod.DMNo),'') as DMNo,
		isnull(rtrim(d.sheetno),'') as sheetno,
		isnull(rtrim(d.pageno),'') as pageno,
		isnull(rtrim(d.currentrevisionno),'') as currentrevisionno,
		isnull(rtrim(d.title),'') as title,
		+ @SQLSelect + 
		isnull(rtrim(d.attached),'') as attached,
		d.modulerecordid,
		isnull(rtrim(d.modulename),'') as modulename,
		d.drawingid,
		isnull(rtrim(d.updatedby),'') as updatedby FROM TAB_NetDrawings d 
		INNER JOIN TAB_NetDM dmod ON  d.drawingid = dmod.DMID
		LEFT OUTER JOIN TAB_NetPickLists Disp ON (d.Disposition_id = Disp.PickId and Disp.Fieldlabelkey='ddlDisposition')
	WHERE d.ModuleRecordID =@modid and d.ModuleName=@modname ORDER BY d.drwid ASC
	SELECT @ErrorCode = @@Error
END
SET NOCOUNT OFF
RETURN @ErrorCode

Thank you very much for the information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 15:01:14

[code]SELECT @SQLSelect =
CASE @language
WHEN 'English' THEN rtrim(isnull(Disp.Englishtext,''))
WHEN 'Spanish' THEN rtrim(isnull(Disp.SpanishText,''))
ELSE rtrim(isnull(Disp.Englishtext,''))
END as Disposition[/code]

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-11 : 15:09:18

Helo Visakh,

How can i include this: + @SQLSelect + in my sql select statement.

Thanks...

quote:

Originally posted by visakh16

SELECT @SQLSelect = 
	CASE 	@language
WHEN 'English' THEN  rtrim(isnull(Disp.Englishtext,''))
WHEN 'Spanish' THEN  rtrim(isnull(Disp.SpanishText,'')) 
ELSE rtrim(isnull(Disp.Englishtext,''))
END  as Disposition

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 15:16:56

Why would you want dynamic sql at all?

ALTER PROCEDURE dbo.SP_GetDrawings
(
	@modid int,
	@modname nvarchar(20),
	@language nvarchar(50)
)
AS

SET NOCOUNT ON

SELECT		d.drwid,
		isnull(rtrim(dmod.DMNo),'') as DMNo,
		isnull(rtrim(d.sheetno),'') as sheetno,
		isnull(rtrim(d.pageno),'') as pageno,
		isnull(rtrim(d.currentrevisionno),'') as currentrevisionno,
		isnull(rtrim(d.title),'') as title,
		CASE @language
			WHEN 'Spanish' THEN rtrim(isnull(Disp.SpanishText, ''))
			ELSE rtrim(isnull(Disp.Englishtext, ''))
		END as Disposition,
		isnull(rtrim(d.attached),'') as attached,
		d.modulerecordid,
		isnull(rtrim(d.modulename),'') as modulename,
		d.drawingid,
		isnull(rtrim(d.updatedby),'') as updatedby
FROM		TAB_NetDrawings AS d 
INNER JOIN	TAB_NetDM as dmod ON d.drawingid = dmod.DMID
LEFT JOIN	TAB_NetPickLists as Disp ON d.Disposition_id = Disp.PickId
			and Disp.Fieldlabelkey = 'ddlDisposition'
WHERE		d.ModuleRecordID = @modid
		and d.ModuleName = @modname
ORDER BY	d.drwid

RETURN @@Error

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-11 : 15:22:54

quote:
Originally posted by Peso

Why would you want dynamic sql at all?
E 12°55'05.25"
N 56°04'39.16"

Agreed. However, to answer your question, you would need to build the entire SELECT statement into a string and exec it in its entirety. You can't combine dynamic and non-dynamic code.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources