Wow,Jeff !
That's just what I needed to start with !
Thank you very much indeed !
Rafael Copquin
----- Original Message -----
From: "Jeff Trusty" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, November 19, 2008 2:47 AM
Subject: Re: stored procedures in SQL
> Rafael,
>
> Creating a stored procedure in that will return a data set in SQL 2005 is
> pretty easy. The syntax to create the procedure is:
>
> For the example you gave, assuming you have a table called Company and a
> PK
> of the CompanyID that is an int datatype.
> Create Procedure dbo.cspCustomerInfo @CustomerId int
> as
> Begin
> Select * from Customer where CustomerId = @CustomerId
> End
>
> I have the procedure name as dbo.cspCustomerInfo. Specifiying dbo means
> that the object is part of the database owner schema. Object ownership is
> something VFP developers don't need to deal with using VFP databases.
> Just
> use dbo all the time unless you really need objects of the same name for
> different users. I also prefix the object name with csp which I think of
> as
> "Custom Stored Procedure". This isn't needed, but it can help you to
> organize things in your head.
>
> @CustomerInfoId is the first parameter name and int is the datatype. You
> can have multiple parameters, just seperate each set of parameter / type
> with a comma.
>
> Because this procedure only has a single SQL statement, you don't have to
> have the Begin and End statements, but it is a good habit to get into.
> Also, it is better to specify the columns you need in the select rather
> than
> the *.
>
> To call the procedure from within VFP, use the following:
>
>
> ConnectionId = SQLStringConnect("Driver={SQL
> Server};Server=ServerName;Database=Pubs;Trusted_Connection=Yes;")
>
> QueryResult = SQLExec( ConnectionId, "exec cspCustomerInfo @CustomerInfoID
> =
> 1", "CursorName")
>
> QueryResult will be > 0 if the query ran successfully. Otherwise you will
> want to call the AERROR() function to populate an array with info on why
> the
> statement failed. If the object is created under the dbo schema, then you
> don't have to specify it in the SQLExec command.
>
> Hope this helps.
>
> Jeff Trusty.
>
> On Tue, Nov 18, 2008 at 5:19 PM, Rafael Copquin
> <[EMAIL PROTECTED]>wrote:
>
>> Perhaps this qualifies as NF, but then, it is a pure VFP question.
>>
>> I am doing my first steps in SQL Server, using SQL Server 2005 Express
>> edition. I just want to create a stored procedure in SQL Server, so I
>> can
>> do the following:
>>
>> Create a connection in a VFP form
>> call the stored procedure (passing for instance a customer PK), so the
>> procedure will return the customer name, address, email, etc.
>>
>> A very simple sql call.
>>
>> But how do you create the procedure in the SQL engine? Or should I ask
>> this
>> question in a SQL forum?
>>
>> TIA
>>
>> Rafael Copquin
>>
>>
>> --- StripMime Report -- processed MIME parts ---
>> multipart/alternative
>> text/plain (text body -- kept)
>> text/html
>> ---
>>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
