Solved

get a binary value using tsql

Posted on 2013-11-13
2
274 Views
Last Modified: 2013-11-13
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
Comment
Question by:g_foulks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39646216
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
 

Author Closing Comment

by:g_foulks
ID: 39646276
Perfect!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question