Asger Blond
ab-mi at post3.tele.dk
Wed Jan 28 16:06:48 CST 2009
John,
The two first arguments in sp_executesql has to use Unicode - that's what
the N means (N for national).
Your "equivalent" may be right in the sense that the second parameter
contains declarations of the variables used in the first parameter. But your
equivalent won't compile... You have to stick to the syntax for
sp_executesql. Following some snips from BOL which may help:
/****** snips start
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
[ @stmt = ] stmt
Is a Unicode string that contains a Transact-SQL statement or batch. stmt
must be either a Unicode constant or a Unicode variable...
[ @params = ] N'@parameter_name data_type [ ,... n ] '
Is one string that contains the definitions of all parameters that have been
embedded in stmt. The string must be either a Unicode constant or a Unicode
variable. Each parameter definition consists of a parameter name and a data
type...
[ @param1 = ] 'value1'
Is a value for the first parameter that is defined in the parameter string.
The value can be a Unicode constant or a Unicode variable. There must be a
parameter value supplied for every parameter included in stmt...
******/ snips end
As to the difference between SET and SELECT when assigning values to
variables (question in your second posting): You can use both but Microsoft
recommends using SET when assigning a constant (a known value) and SELECT
only when the value is retrieved from a table. I think this practice makes
the code more readable.
BTW: Reading your previous postings it looks like you are also asking HOW TO
RETRIEVE AN OUTPUT VALUE WHEN CALLING A SP.
My usp_MinMaxPKID don't expose any output parameters so the result is
completely handled by the sp itself. If you want a more flexible sp where
you can handle the result when calling the sp you have to use output
paramenters like this:
CREATE PROCEDURE usp_MinMaxPKID
@tblName varchar(100), @MinimumPKID int OUTPUT, @MaximumPKID int OUTPUT
AS
DECLARE @MinPKID int, @MaxPKID int, @sql nvarchar(100);
SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName;
EXEC sp_executesql @sql, N'@PKID int OUTPUT', at PKID=@MinPKID OUTPUT;
SET @sql = N'SELECT @PKID = max(PKID) FROM ' + @tblName;
EXEC sp_executesql @sql, N'@PKID int OUTPUT', at PKID=@MaxPKID OUTPUT;
SET @MinimumPKID = @MinPKID
SET @MaximumPKID = @MaxPKID
GO
You can then execute the sp like this (managing and formatting the output
values as you wish):
DECLARE @min int, @max int
EXEC usp_MinMaxPKID 'MyTable', @min OUTPUT, @max OUTPUT
SELECT @min AS [This is minimum PKID], @max AS [This is maximum PKID]
GO
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 28. januar 2009 18:09
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Stored Procedure return value
Asger,
Thanks for the response. So that I can actually understand
what I am doing, I have a couple of questions:
> SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName;
What does the N' do?
> EXEC sp_executesql @sql, N'@PKID int OUTPUT',
@PKID=@MinPKID OUTPUT;
is this the equivalent of:
DECLARE @PKID int OUTPUT
EXEC sp_executesql @sql, @PKID
Your code looks like perhaps it is dimensioning variables in
the same line it is using them or something. Needless to
say I am not there yet so understanding (and thus modifying
to my needs) the code is difficult.
John W. Colby
www.ColbyConsulting.com
Asger Blond wrote:
> John,
>
> Have a look at sp_executesql, which allows you to send the value of an
> output parameter to a variable.
>
> You could create a sp like this:
>
> CREATE PROCEDURE usp_MinMaxPKID @tblName varchar(100)
> AS
> DECLARE @MinPKID int, @MaxPKID int, @sql nvarchar(100);
> SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MinPKID OUTPUT;
> SET @sql = N'SELECT @PKID = max(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MaxPKID OUTPUT;
> SELECT @MinPKID AS MinPKID, @MaxPKID AS MaxPKID;
> GO
>
> Then execute the sp like this:
>
> EXEC usp_MinMaxPKID 'MyTable';
> GO
>
> HTH
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
> Sendt: 27. januar 2009 19:39
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Stored Procedure return value
>
> I am not going to get into a "dynamic SQL" debate. Simply
> understand that I run SQL Servers that have exactly and only
> one person accessing them, ME.
>
> I have literally many dozens of databases, each with dozens
> of queries and tables. I am not going to edit the stored
> procedures in each database I create in order to avoid an
> injection attack that will never come.
>
> In my case it does initially complicate what I want to do
> but it simplifies the big picture. And once I learn the
> technique, whatever it might be, then it will no longer
> complicate my life.
>
> I have found an article about exactly what I am trying to
> do, but of course it is just complicated enough that I have
> to study it very carefully in order to figure out how the
> heck they are doing it.
>
> Thanks for your example, I will work with it as well.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Arthur Fuller wrote:
>> Hi John,
>>
>> I must confess that I am not a fan of dynamic SQL, and that in your case
> it
>> complicates things.
>>
>> Normally (e.g. static SQL) one would write something like this:
>>
>> SET @minID = (SELECT Min(PKID) FROM myTable)
>> and similarly for @maxID
>>
>> I haven't actually tried doing this in dynamic SQL but I think the same
>> thing should work from there too. The trick is the SET part of the
>> statement.
>>
>> hth,
>> Arthur
>>
>> On Tue, Jan 27, 2009 at 8:50 AM, jwcolby
> <jwcolby at colbyconsulting.com>wrote:
>>> Arthur,
>>>
>>> What I still don't understand is how I get the data into the
>>> variables to return INSIDE of the stored procedure.
>>>
>>>
>>> For example, I might have a table with the PKID starts at
>>> ~13 million and go to 87 million. My external process can
>>> only process 1 million records maximum so I need to grab 1
>>> million record chunks of data.
>>>
>>> I need to get the min(PKID) and max(PKID) and I need to
>>> return these to code out in Access so that I can then "get
>>> the next million records" based on the PKID. I want to do a
>>> bulk export starting at 13 million to 14 million, then when
>>> that works, 14 to 15 million etc.
>>>
>>> I have the SP written and tested that does that bulk export,
>>> now all I need is the min(PKID) and Max(PKID) so I can start
>>> carving up the table.
>>>
>>> I have written a usp_MinMaxPKID @tblName varchar, @MinPKID
>>> int output, @MaxPKID int output
>>>
>>> So I have a SP with the values passed in and declared OUTPUT
>>> (return value to calling procedure). In the past I have
>>> written dynamic code something like:
>>>
>>> declare @SQL = 'SELECT Min(PKID) as MinPKID, Max(PKID) as
>>> MaxPKID from dbo.' + @tblName.
>>>
>>> exec(@sql)
>>>
>>> This had the effect of getting the min and max from the
>>> table passed into @tblName, but it places the results into
>>> the results pane in the query window when I execute the SP.
>>>
>>> Now I need to somehow place the results into the @MinPKID
>>> and @MaxPKID. It is this piece that I am "missing".
>>>
>>> Any help (from anyone) on how to do this would be much
>>> appreciated.
>>>
>>> John W. Colby
>>> www.ColbyConsulting.com <http://www.colbyconsulting.com/>
>>>
>>>
>>> Arthur Fuller wrote:
>>>> >From the FE:
>>>>
>>>> declare a variable called intRowCount and set its value to zero.
>>>>
>>>> >From the BE's proc:
>>>>
>>>> Declare a parm called @intRowCount OUTPUT
>>>>
>>>> Execute the proc, passing your parm in.
>>>>
>>>> Interrogate the variable intRowCount upon return to your external code.
>>>>
>>>> That should do it.
>>>>
>>>> If this is unclear, I could whip up a toy program for you, but I think
>>> that
>>>> this ought to be clear enough for you to proceed. If not, say so and I
>>> shall
>>>> supply a toy program that demo's this approach.
>>>>
>>>> A.
>>>>
>>>> On Fri, Jan 23, 2009 at 5:35 PM, jwcolby <jwcolby at colbyconsulting.com
>>>> wrote:
>>>>
>>>>> can the sp be tested from the query window in SQL Server.
>>>>> IOW, I open a query, EXEC the sp_CountRec and pass in ... what?
>>>>>
>>>>> John W. Colby
>>>>> www.ColbyConsulting.com <http://www.colbyconsulting.com/> <
>>> http://www.colbyconsulting.com/>
>>> >>
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com