CREATE PROCEDURE [dbo].[USP_NewPicklist]

		(@pickid			int,
		@language		nvarchar(50),
		@fieldlabelkey				nvarchar(200),
		@englishdescription		nvarchar(100),
		@englishtext	nvarchar(200),
		@LangText	nvarchar(100),
		@LangDescription	nvarchar(200),
		@UserName				nvarchar(50))
AS

DECLARE @ErrorCode				int
DECLARE @SQLStatement 			nvarchar(2000)
DECLARE @SQLSelect				nvarchar(1000)
DECLARE @SQLFields				nvarchar(500)
DECLARE @MaxPickId					Int

	SET @MaxPickId = (SELECT  MAX(pickID) FROM TAB_ccsNetPickLists WHERE fieldlabelkey= @fieldlabelkey)


	IF @MaxPickId is null SET @pickid = '1'
	ELSE 
	BEGIN
		SET @pickid = @MaxPickId + 1
	END

SET NOCOUNT ON

Select @SQLFields = 
	CASE 	@language
		WHEN 'Spanish' THEN ' Spanishtext, Spanishdescription,'
		WHEN 'Catalan' THEN 'Catalantext, Catalandescription,'
		ELSE 'Spanishtext, Spanishdescription,'
END

SELECT @SQLSelect = 
	CASE 	@language
		WHEN 'Spanish' THEN ' + char(39) + @LangText + Char(39) + ',' + char(39) + @LangDescription + Char(39) + '
		WHEN 'Catalan' THEN ' + char(39) + @LangText + Char(39) +','+ char(39) + @LangDescription + Char(39) + '
		ELSE ' + char(39) + @LangText + Char(39) + ','+ char(39) + @LangDescription + Char(39) + '
END

SET @SQLStatement = 'INSERT INTO TAB_ccsNetPickLists(pickid,fieldlabelkey,UpdatedBy,englishtext,englishdescription,' + char(39)+ @SQLFields + char(39)+ ')
			Values('+ convert(nvarchar(50), @pickid) + ',' + char(39)+ @fieldlabelkey +char(39)+ ','+char(39)+ @UserName +char(39)+ ',' +char(39)+ @englishtext +char(39)+ ', ' + char(39)+ @englishdescription +char(39)+ ',' + char(39)+ @SQLSelect + char(39)+ ')' 


print (@SQLStatement)
---EXEC (@SQLStatement)
SELECT @ErrorCode = @@Error

SET NOCOUNT OFF
RETURN @ErrorCode

quote:

---

Originally posted by visakh16

what's the need of dynamic sql here? why cant you simply use main statement itself. whats the purpose of all these varchars variables and EXEC() ?

---

Declare @Language varchar(20),@SqlSelect varchar(2000)
set @Language = 'Spanish'
SELECT @SQLSelect =  CASE 	@language
		WHEN 'Spanish' THEN ' + char(39) + @LangText + Char(39) + '','' + char(39) + @LangDescription + Char(39) + '
		WHEN 'Catalan' THEN ' + char(39) + @LangText + Char(39) + '' , ''  + char(39) + @LangDescription + Char(39) + '
		ELSE ' + char(39) + @LangText + Char(39) + '','' + char(39) + @LangDescription + Char(39) + '
END

select @SqlSelect

INSERT INTO TAB_ccsNetPickLists(pickid,fieldlabelkey,UpdatedBy,englishtext,englishdescription,Spanishtext, Spanishdescription,Catalantext, Catalandescription)
SELECT convert(nvarchar(50), @pickid) , 
@fieldlabelkey,
@UserName,
@englishtext,
@englishdescription,
CASE @language
WHEN 'Spanish' THEN @LangText
ELSE NULL
END,
CASE @language
WHEN 'Spanish' THEN @LangDescription
ELSE NULL
END,
CASE @language
WHEN 'Catalan' THEN @LangText
ELSE NULL
END,
CASE @language
WHEN 'Catalan' THEN @LangDescription
ELSE NULL
END

quote:

---

Originally posted by rohitkumar

visakh gave you the answer why are you so adamant on using case in the list of columns? that wont work.

---

Declare @SqlSelect varchar(2000), @SqlSTatement varchar(2000),@SqlFields Varchar(2000)

Declare @pickid			int,
		@language		nvarchar(50),
		@fieldlabelkey				nvarchar(200),
		@englishdescription		nvarchar(100),
		@englishtext	nvarchar(200),
		@LangText	nvarchar(100),
		@LangDescription	nvarchar(200),
		@UserName				nvarchar(50)


set @Language = 'Spanish'
set @PickID = 1
set @FieldLabelKey = 'hello test'
set @EnglishDescription = 'THIS IS MY ENGLISH DESCRIPTION'
set @EnglishText = 'THIS IS MY ENGLISH TEXT'
set @LangTEXT = 'THIS IS MY LANG TEXT'
set @LangDESCRIPTION = 'THIS IS LANG DESCRIPTION'
set @UserName = 'Vinnie'


Select @SQLFields = 
	CASE 	@language
		WHEN 'Spanish' THEN ' Spanishtext, Spanishdescription'
		WHEN 'Catalan' THEN 'Catalantext, Catalandescription'
		ELSE 'Spanishtext, Spanishdescription,'
END


SELECT @SQLSelect =  CASE 	@language
		WHEN 'Spanish' THEN char(39) + @LangText + Char(39) + ',' + char(39) + @LangDescription + Char(39)  
		WHEN 'Catalan' THEN char(39) + @LangText + Char(39) + ','  + char(39) + @LangDescription + Char(39)
		ELSE  char(39) + @LangText + Char(39) + ',' + char(39) + @LangDescription + Char(39) 
END

--select @SqlSelect


SET @SQLStatement = 'INSERT INTO TAB_ccsNetPickLists(pickid,fieldlabelkey,UpdatedBy,englishtext,englishdescription,' +  @SQLFields + ')	
Values('+ convert(nvarchar(50), @pickid) + ',' + char(39)+ @fieldlabelkey +char(39)+ ','+char(39)+ @UserName +char(39)+ ',' +char(39)+ @englishtext +char(39)+ ', ' + char(39)+ @englishdescription +char(39)+ ',' + @SQLSelect + ')' 

Exec( @SqlStatement)