Having problems with sp_executesql in SQL Server stored procedures

Hi, I'm having problems with returning a result from sp_executesql with parameters.  The idea is to provide a Japanese character (romaji/katakana/hirigana), and a conversion from and to a character type.

SELECT [KanaID] ,[romaji],[hiragana] ,[katakana] FROM [dbo].[Kana] where romaji = 'do'

Open in new window


returns

KanaID      romaji       hiragana  katakana
-------------- -------------- -------------- --------------
61              do              ど               ド

(1 row(s) affected)

Ideally I'm trying to run the following:

EXEC	 [dbo].[ConvertKana]
		@character = N'do',
		@FromType = N'romaji',
		@ToType = N'hiragana'

Open in new window


with an end result something like:

-----


but instead I'm getting NULL.

From playing around with it, it looks like there;s something wrong with my execution of sp_executesql. Parameters not getting passed to or from the SQL statement maybe?

Here is the stored procedure code:

CREATE PROCEDURE [dbo].[ConvertKana] 
	-- Add the parameters for the stored procedure here
	@character nvarchar(5) = '0',
	@FromType nvarchar(15) = '0',
	@ToType nvarchar(15) = '0'

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @SQL NVARCHAR(2000)
	DECLARE @ParmDefinition NVARCHAR(2000)
	DECLARE @Result NVARCHAR(5)

	SET @SQL = N'SELECT @pResult = @pToType From rai.dbo.kana where @pFromType = @pCharacter'
	SET @ParmDefinition = N'@pCharacter nvarchar(5), @pFromType nvarchar(15), @pToType nvarchar(15), @pResult nvarchar(5) OUTPUT'

	Select @Totype, @FromType, @character

	EXEC sp_executesql @SQL, @ParmDefinition, @character,@FromType,@ToType, @pResult = @Result OUTPUT

	SELECT @Result

END

Open in new window



This has been driving me nuts for a day now, and I'm sure its just something stupidly simple I am overlooking. Help??

Thanks
AngusTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This
	SET @SQL = N'SELECT @pResult = @pToType From rai.dbo.kana where @pFromType = @pCharacter'

Open in new window

just compares and returns the variable/parameters values. You'll have to create dynamic SQL command with the column names:
	SET @SQL = N'SELECT @pResult = ' + @pToType + ' From rai.dbo.kana where ' + @pFromType + ' = @pCharacter'

Open in new window

2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ioannis ParaskevopoulosCommented:
I do agree with Qlemo, but i would like to also advise on how to "debug" such cases. You could PRINT your SQL statement and try running it directly against your DB. That way you could be able to find the reason yourself.

Giannis
1
AngusTAuthor Commented:
So I end up with the more elegant solution by the following

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @ParmDefinition NVARCHAR(MAX)
	DECLARE @Result NVARCHAR(5)

	SET @SQL = N'SELECT @Result = ' + @ToType + ' From rai.dbo.kana where '+ @FromType +' = '''+ @Character +''''
	SET @ParmDefinition = '@Result nvarchar(5) OUTPUT'

	EXEC sp_executesql @SQL, @ParmDefinition, @Result = @Result OUTPUT

	SELECT @Result + ' is the result'

END

Open in new window



So most of these parameters could have just been passed by building a dynamic string before using the sp_executesql and I only needed the OUTPUT parameter to get it to work.

Since this was mostly an exercise in understanding how to use parameters in sp_executesql, can you suggest any scenarios where an input parameter might be necessary? Here I am clearly just passing the stored procedure parameter as a value to build the SQL string and there was no need for them at all.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You should still use parameters for values, as shown by me earlier, but here the complete code:
CREATE PROCEDURE [dbo].[ConvertKana] 
  -- Add the parameters for the stored procedure here
  @character nvarchar(5) = '0',
  @FromType nvarchar(15) = '0',
  @ToType nvarchar(15) = '0'
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    -- Insert statements for procedure here
  DECLARE @SQL NVARCHAR(2000)
  DECLARE @ParmDefinition NVARCHAR(2000)
  DECLARE @Result NVARCHAR(5)

  SET @SQL = N'SELECT @pResult = ' + @pToType + ' From rai.dbo.kana where ' + @pFromType + ' = @pCharacter'
  SET @ParmDefinition = N'@pCharacter nvarchar(5), @pResult nvarchar(5) OUTPUT'

  Select @Totype, @FromType, @character

  EXEC sp_executesql @SQL, @ParmDefinition, @character, @pResult = @Result OUTPUT

  SELECT @Result
END

Open in new window

1
AngusTAuthor Commented:
So it can only be used as traditional variable values. The parameters can't represent information like table or column names. @ToType and @FromType, which represent columns in a table can't be passed in as paramters to build the SQL String. Only the @character value can, right?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Correct.
1
AngusTAuthor Commented:
Many thanks for the info. :) There's a LOT of documentation on this function, but little aspects like this seem hard to find in it. Learned something new :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.