Solved

SQL Stored Procedure variables

Posted on 2004-07-30
12
1,930 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

789 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