need help: new at store procedure

HI,
I want to create Store Procedure that Do:
I have 2 tables (A,B) and i want to know if theres records equal
between A.id1 in B.id2 or in B.id3
I want to call this Store Procedure from Asp (need to know how)
I work with SQL Server.
Regardsmoshe wrote:
> HI,
> I want to create Store Procedure that Do:
> I have 2 tables (A,B) and i want to know if theres records equal
> between A.id1 in B.id2 or in B.id3
> I want to call this Store Procedure from Asp (need to know how)
> I work with SQL Server.
> Regards
Create a SQL login and add the user to your database. Let's assume you call
the login "asplogin" with a password of "nigolpsa"
In Query Analyzer, run this script:
Create PROCEDURE CompareData AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM A INNER JOIN B ON A.id1=B.id2) OR
EXISTS (SELECT * FROM A INNER JOIN B ON A.id1=B.id3)
RETURN 1
ELSE
RETURN 2
go
GRANT EXECUTE ON CompareData TO asplogin
go
In ASP (I'm assuming you mean classic ASP since you did not specify
ASP.Net), follow the procedure described here to add the ADO type library to
your application's global.asa file :
http://www.aspfaq.com/show.asp?id=2112
Then create a page with this code (untested - there may be typos or syntax
errors. This should give you the idea. Look up the correct syntax in the ADO
reference at
http://msdn.microsoft.com/library/e...pireference.asp)
:
<%
dim cn, cmd
set cn=createobject("adodb.connection")
set cmd=createobject("adodb.command")
With cmd
.CommandType=adCmdStoredProc
.CommandText="CompareData"
set .ActiveConnection = cn
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger,adParamReturnValue)
end with
cn.Open "Provider=SQLOLEDB; " & _
"Data Source=YourServerName; " & _
"Initial Catalog=YourDatabaseName;" & _
"User ID=asplogin;Password=nigolpsa"
cmd.Execute
If cmd.Parameters(0) = 1 Then
Response.Write "Matching records exist"
Else
Response.Write "No matching records exist"
End if
cn.close: set cn=nothing
%>
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"