Solved

get a binary value using tsql

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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