Solved

get a binary value using tsql

Posted on 2013-11-13
2
271 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

777 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