Solved

SQL Stored Procedure variables

Posted on 2004-07-30
12
1,932 Views
Last Modified: 2008-03-03
Please help! I am sure this will be really easy for someone. I am a relative noob to TSQL and stored procedure syntax. I have a stored procedure that takes one argument, queries a table for a match to the argument, and sets the result equal to a variable (so I can use this result later on in the stored procedure). For some reason, this will not work...

Stored Procedure:
-------------------------------------------------------------------------
CREATE PROCEDURE dbo.sp_po_publishrequest
      @strPoUpdRequestID uniqueidentifier
AS
DECLARE @strPoUpdRequestFileID uniqueidentifier
SET @strPoUpdRequestFileID = (SELECT poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID)
SELECT @strPoUpdRequestFileID
GO
--------------------------------------------------------------------------

I keep getting this when I run the SP in SQL Query Analyzer:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

Can anyone help?
0
Comment
Question by:jnelson35
[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
12 Comments
 

Author Comment

by:jnelson35
ID: 11677071
Sorry, forgot to mention...this is SQL 2000 that I am dealing with (if it matters)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11677105
try

CREATE PROCEDURE dbo.sp_po_publishrequest
     @strPoUpdRequestID uniqueidentifier
AS
SELECT poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID
GO
0
 
LVL 3

Expert Comment

by:pg_india
ID: 11677109
SET @strPoUpdRequestFileID = (SELECT poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID)

instead of thsi try:


SELECT@strPoUpdRequestFileID = poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jnelson35
ID: 11677174
pg_india,

I have tried both ways, neither works...I was using the SET method because I thought it was supposed to be the ANSI standard for setting variables.

hilarie,

Yours doesn't work either, which is really strange.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11677277
How do you call the stored procedure ?

BOL Say
uniqueidentifier A globally unique identifier (GUID).

Remarks
A column or local variable of uniqueidentifier data type can be initialized to a value in two ways:
- Using the NEWID function.
- Converting from a string constant in the following form (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f). For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

Maybe you could explain what you intend to do with this stored procedure ?
0
 

Author Comment

by:jnelson35
ID: 11677352
Okay, this db is a sort of document index. Files are uploaded (in binary) to one table that also contains the FileID, FileName, FileType, and FileSize. The uploads are indexed in a separate table along with some other information and linked to the correct record in the file table by the FileID.

I want my SP to retrieve the FileID from the index table and then delete the appropriate record in the file table.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11677667
You don't need this two-step approch to delete a record from the table

You can use JOINs in a delete statement
eg

delete a
from Uploads a
INNER JOIN FileInfo b ON a.FileID = b.FileID
where b.FileName = 'FileToDelete'

then
delete from FileInfo where FileName = 'FileToDelete'

The whole thing could be wrapped in a stored procedure to handle transaction nicely

Please post table names + table structures if you need more support

Cheers

Hilaire
0
 

Author Comment

by:jnelson35
ID: 11678134
Okay...here are the details on my 2 tables...

tbl_po_web_temp
     |
     poUpdRequestID uniqueidentifier
     poID uniqueidentifier
     poName char(25)
     poTitle char(200)
     poOrg char(10)
     poNumPages int
     poReview datetime
     poUpdRequestDate datetime
     poUpdRequestRemarks char(200)
     poUpdRequestType smallint
     poUpdRequestFileID uniqueidentifier

tbl_po_docs
     |
     poUpdRequestFileID uniqueidentifier
     poUpdRequestFileType char(50)
     poUpdRequestFileSize varchar(50)
     poUpdRequestFileName char(50)
     poUpdRequestFileData image
0
 
LVL 5

Expert Comment

by:hkamal
ID: 11678332
Try this:

CREATE PROCEDURE dbo.sp_po_publishrequest
     @strPoUpdRequestID uniqueidentifier
AS
DECLARE @strPoUpdRequestFileID uniqueidentifier
SELECT @strPoUpdRequestFileID = poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID
SELECT @strPoUpdRequestFileID
GO

0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11678483
May I also ask why you choosed the uniqueidentifier datatype rather than an int identity field (roughly the same than an oracle sequence, automatically generates incremental numbers) ?

Do you use replication between several databases ?

The following statement will delete records in tbl_po_web_temp
that match a given filename in tbl_po_docs

delete a
from tbl_po_web_temp a
inner join tbl_po_docs b on a.poUpdRequestFileID  = b.poUpdRequestFileID
where b.poUpdRequestFileName = 'file'


Below is an exemple stored procedure with trasaction handling to perform two deletes

create proc _usp_sampleproc_delete (@filename varchar(50)) as
begin transaction
delete a
from tbl_po_web_temp a
inner join tbl_po_docs b on a.poUpdRequestFileID  = b.poUpdRequestFileID
where b.poUpdRequestFileName = @filename
if @@error<> 0
begin
   rollback transaction
end
else
begin
   delete tbl_po_docs where poUpdRequestFileName = @filename
   if @@error = 0
   begin
      commit transaction
   end
   else
   begin
      rollback transcation
   end
end
go

exec proc _usp_sampleproc_delete 'yourfile.pdf'

You could use the same logic for an update statement

Hope this helps

Hilaire
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
ID: 11678873
The odds are that you are attempting to call your procedure in QA like this:

Exec sp_po_publishrequest {DE8F841E-B3A8-4722-960A-A71A02312E52}

Add quotes around the uniqueidentifier, and it should work:

Exec sp_po_publishrequest '{DE8F841E-B3A8-4722-960A-A71A02312E52}'

NOTE:  you should also avoid using sp_ as the start of a stored procedure name, because SQL Server will look in the master database for any procedure with a name starting in sp_ before looking in the current database.  I tend to use usp_ - others use App or Concept starts (e.g. PO_PublishRequest)
0
 

Author Comment

by:jnelson35
ID: 11679040
Thank you all for help, but bhess1's solution answered my question. I will use all your tips as I continue to delve into the fun world of SQL.

Thanks again :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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