?
Solved

SQL Stored Procedure variables

Posted on 2004-07-30
12
Medium Priority
?
1,933 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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:
Brendt Hess earned 500 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 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