How to test a stored procedure ? how ?

Ok ,
I'm new in stored procedures, I created one, so I want to test it without
going and coding a program in C# or VB.NET, is it any way to run it where it
will ask my parameters and get the result using the SQL Enterprise Manager ?
ThanksEasiest in Query Analyzer:
EXEC usp_proc_name
@.param1 = 123,
@.param2 = 'ABC'
David Portas
SQL Server MVP
--|||In addition to executing within Query Analyzer, there is also a feature to
run the procedure step by step in debug mode. Read up on "Transact-SQL
Debugger" in Books Online or MSDN.
"Carlos" <cp@.swa.com> wrote in message
news:%23$UsSnCyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Ok ,
> I'm new in stored procedures, I created one, so I want to test it without
> going and coding a program in C# or VB.NET, is it any way to run it where
> it will ask my parameters and get the result using the SQL Enterprise
> Manager ?
> Thanks
>|||Ok I used the Query analyzer now where do I see my returned value ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
> Easiest in Query Analyzer:
> EXEC usp_proc_name
> @.param1 = 123,
> @.param2 = 'ABC'
> --
> David Portas
> SQL Server MVP
> --
>|||Pass a variable and specify OUTPUT. A nice feature of Query Analyzer is
that it will script the EXEC statement for you. Press F8 to open the
Object Browser, right-click on your proc, click "Script Object to new
window as" and then "EXECUTE". Result:
DECLARE @.RC int
DECLARE @.param1 int
DECLARE @.param2 varchar(10)
-- Set parameter values
EXEC @.RC = [JUNK].[dbo].[usp_proc_name] @.param1 OUTPUT , @.param2

>From the same point in the Object Browser you can also access the
debugger as JT suggested.
David Portas
SQL Server MVP
--|||declare @.x as int
If you want to print the result of an output parameter:
exec myproc @.a=1, @.b=2, @.c = @.x output
print @.x
If you want to print the result of a return value:
EXECUTE @.x = myproc @.a=1, @.b=2
print @.x
"Carlos" <cp@.swa.com> wrote in message
news:ufE8JxCyFHA.448@.TK2MSFTNGP11.phx.gbl...
> Ok I used the Query analyzer now where do I see my returned value ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
>|||Can you post a sample of what you are trying to test? It would be much
easier for us to determine what you exactly want to test.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Carlos" <cp@.swa.com> wrote in message
news:ufE8JxCyFHA.448@.TK2MSFTNGP11.phx.gbl...
> Ok I used the Query analyzer now where do I see my returned value ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
>|||Hi Carlos,
Here are some articles for your reference about how to debug SQL Server
stored procedures and the necessary configuration settings and steps for
each approach
HOW TO: Debug Stored Procedures in Visual Studio .NET
http://support.microsoft.com/kb/316549/EN-US/
Troubleshooting tips for T-SQL Debugger in Visual Studio .NET
http://support.microsoft.com/kb/817178
Debugging Stored Procedures
http://www.windowsitpro.com/Article...6356/16356.html
You may also search keywords debug SQL stored procedure in google.com for
more
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

How to stored a stored procedures results into a table

Hi, How can I store a stored procedure's results(returning dataset) into
a table?

Bob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Bob James" <BJ@.system.com> wrote in message news:41ed4eb1$1_2@.127.0.0.1...
> Hi, How can I store a stored procedure's results(returning dataset) into
> a table?
> Bob
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

insert into dbo.MyTable
exec dbo.MyProc @.parm1 = 'foo'

Where dbo.MyTable already exists and matches the structure (columns and data
types) of the procedure's result set. But there are a number of issues with
this approach:

http://www.sommarskog.se/share_data.html#INSERTEXEC

If you read the whole article you might find one of the other approaches
suits your needs better anyway, so you can avoid those issues.

Simon

How to stop data redundancy

Hey guys...i've got a problem wif my stored procedures...in which my page keep repeating the same data...so to counter this problem i use SELECT DISTINCT instead of just SELECT..but the problem is when i change SELECT into SELECT DISTINCT...the page will not be display,page error...for your information my stored procures was auto generate from some security sofware...so can guys help me out with my code...

SELECT DISTINCT t.TargetID,
'V' RecordType,
t.TargetDNSName [Target DNS Name],
t.TargetIPAddress [Target IP],
t.TargetIPDisplay [Target IP Display],
t.TargetOSName,
t.TargetOSRevisionLevel,
v.SecChkID,
v.Severity,
sc.TagName [Tag Name],
sc.ChkBriefDesc [Tag Brief Desc],
sc.ChkName [Tag Chk Name],
CONVERT(NVARCHAR(4000),sc.ChkDetailDesc) [Tag Detail Desc],
CONVERT(NVARCHAR(4000),r.RemedyDesc) [Remedy],
o.ObjectID,
o.ObjectTypeDesc [Object Type Desc],
o.ObjectName [Object Name],
s.SensorDataID,
a.AttributeName,
a.AttributeValue,
NULL [Port],NULL [Service Name],NULL [Protocol]

FROM #Vulns v
INNER JOIN TargetHost t (NOLOCK)
ON v.TargetID = t.TargetID
INNER JOIN (SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
INNER JOIN ObjectView o (NOLOCK)
ON v.ObjectID = o.ObjectID
LEFT OUTER JOIN SensorData1 s WITH (NOLOCK, INDEX(SensorData1_AK3))
ON v.ObservanceID = s.ObservanceID
AND s.Cleared = 'n'
LEFT OUTER JOIN SensorDataAVP a (NOLOCK)
ON s.SensorDataID = a.SensorDataID
AND a.AttributeValue IS NOT NULL
AND a.AttributeValue != ''
UNION ALL

In the FROM clause, please notice that there is an INNER JOIN statement that has no ON condition but was directly followed a LEFT OUTER JOIN as specified on this line:
INNER JOIN (SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)

I don't know if you have intended to do this, but this statement looks weird. Please try to review this clause.

|||

Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID

|||

selipeh wrote:

Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID

That is not the correct syntax for SQL Server as far as I amaware. You say you are getting an error. Please provide the errortext.|||thanks tmorton,actually there is no error...the code was fine...the only problem is when it comes to display process...it won't display anything just "page cannot be display"...but when i delete the DISTINCT it will display my .aspx page with a lot of data redundancy....|||What happens when you run your stored procedure through Query Analyzer?|||

i check in sql analyzer...but there was no error...just simply it won't display anythings,but when i delete DISTINCT...it will be fully functioning with data redundancy

|||Well, that makes no sense. The removal of the DISTINCT keyword inand of itself would not cause a query to stop returning results. My guess is that more changed than the removal of the DISTINCT keyword.|||

As far as I am aware the query isn't correct either.

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
should be

INNER JOIN(SELECT * FROMSecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID) somename
ON v.SecChkID = sc.SecChkID

|||On top of that, I'm not sure what the UNION ALL is supposed to be doing at the end of the query.|||The UNION ALL operator is used in old style ANSI SQL JOINs and that is the reason for the duplicates because the UNION operator performs an implict distinct by removing duplicates but UNION ALL leaves duplicates in. I think the UNION ALL operator could be changed to one or two AND operators. Hope this helps.|||

I know what UNION ALL does, but it isn't syntatically correct as the last thing in a query. UNION ALL *MUST* be followed by a second query in which case it does caddre said. It combines the output of both queries and doesn't remove the duplicates.

Toss this in Query Analyzer:

SELECT '1'
UNION ALL

You get a syntax error.

|||You get syntax error because there is no UNION in you query but the poster's UNION ALL is at the end of 5 JOIN operations and 3 AND operators so SQL Server maybe ignoring some of the JOINS because the CONVERT sometimes makes tables UNION compatible and running UNION ALL instead. I am not saying the query cannot be modified but it works now so replace the UNION ALL with AND operators to start.|||

3rd syntax error:

INNER JOIN TargetHost t (NOLOCK)

should be

INNER JOIN TargetHost t WITH (NOLOCK)

Infact that's done a lot in the query, and they are all wrong. The query as posted won't run period, and should toss up an error. If it is not, it's only because with so many syntax errors you have thoroughly confused the T-SQL parser.

|||The poster said the code runs, you are running DML(data manipulation language) without DDL(data definition language). That is the reason SQL forums always ask for DDLs so when the person post some DDL you can run it in RedGate or BMC. I have TOAD here because I run Oracle.