Solved

Accessing SQL variables within"WITH" statement

Posted on 2014-04-23
7
216 Views
Last Modified: 2014-04-29
Hi
I'm trying to add an input variable (part_number) to my SQL Stored Proc but it seems to ignore the value I pass into the Stored Proc.

The only odd thing with the Stored Proc is that it uses the "WITH" convention to optimise it, and I'm wondering if you have to access a variable differently when used within the "WITH" convention? thanks
Fergal
0
Comment
Question by:fjkilken
  • 3
  • 2
  • 2
7 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40017542
No,  but are you using this parameter in your sproc?
0
 

Author Comment

by:fjkilken
ID: 40017574
Yes, it's being refernced in the proc, I'm trying to pass a "part_number" value into the proc
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40017714
Well, then you should post your procedure and it's invokation. Cause my Crystal Ball (tm) is not working right now..Crystal Ball (tm)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:fjkilken
ID: 40017762
ha ha - good point, I'm just trying to trim-down the query now to the pertinent code, but the issue is that now the parameter IS being recognised, so I'm trying to determine from the removed code as to what could be causing the parameter to be blocked.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40026354
You do realize we have no idea what you are talking about, right?  

If you want help, try posting your query and explain what you are trying to achieve.
0
 

Author Comment

by:fjkilken
ID: 40027742
Thanks for the comments - after cleaning-up my query in prep. for posting it here, my variable is now functioning as expected, I'm not sure exactly what fixed it but it's now working.
In relation to this however, I need to be able to pass-in multiple values as in a SQL "IN" query, for example;
instead of simply using a single value-

WHERE @StockNumber = 'ABC'

I'd like to be able to do:

WHERE @StockNumber IN ('ABC','123','XYZ')

- is this possible?
Thanks!
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40027992
Yes.  There are a number of ways (from good to bad):
1. Table-Valued parameters.
2. Xml
3. Delimted string.
4. Dynamic SQL.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 74
SQL DATEADD 10 79
Linked Server - SP with Param to VIew 7 19
Why is this SQL bringing back extra rows? (parsing XML data) 4 33
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
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…

713 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