• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

get a binary value using tsql

I have a Sql 2005 database with a test table containing one record. four columns (3 char and 1 varbinary(max)

If I write a query like this I get the returned record

select phrase_content from phrase_index where
application ='cs' and
phrase_number = '1' and
language = 'eng' and
gender = 'female'

Open in new window


when I write a stored procedure to pass in the input and get the output of phrase_content all I get is a null value. What am I doing wrong? Why can't I get the value of phrase_content when using a stored procedure?

ALTER PROCEDURE [dbo].[get_PhraseContent] 
	-- Add the parameters for the stored procedure here

@application char,
@phrase_number char,
@language char,
@gender char,
@phrase_content binary OUTPUT

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



    -- Insert statements for procedure here
	SELECT @phrase_content = phrase_content from phrase_index where 
	application = @application and
	phrase_number = @phrase_number and
	language = @language and
	gender = @gender
	
END

Open in new window

0
g_foulks
Asked:
g_foulks
1 Solution
 
Scott PletcherSenior DBACommented:
You need to put the appropriate lengths on the parameters -- I think by default they are only one byte long for char.

For best performance, you should also use exactly the same data type as the column they are being compared to:


ALTER PROCEDURE [dbo].[get_PhraseContent]
      -- Add the parameters for the stored procedure here

@application varchar(30), --<<-- chg to match data type of "application" column
@phrase_number int, --<<-- chg to match data type of "phrase_number" column
@language varchar(40)),  --<<-- chg to match data type of "language" column
@gender char(6),  --<<-- chg to match data type of "gender" column
@phrase_content varbinary(2000) OUTPUT --<<-- change to match data type of "phrase_content" column
0
 
g_foulksAuthor Commented:
Perfect!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now