UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dynamic Parameter Stored Procedures, SQL Server 2012    
 
   
mrpersonality
post May 11 2019, 05:19 PM
Post#1



Posts: 465
Joined: 4-December 02
From: NSW Australia


Can someone explain to me - in the example below that i googled for stored proc with parameters
why are the parameters also listed after the Exec statement, is that what paramatizes them ?
i know a similar proc will also run without those parameters at the end
CREATE PROCEDURE [dbo].[PROC001]
@userID varchar(20),
@pwdHash varchar(20),
@optionalParam1 NVARCHAR(50) = NULL -- Other optional parameters
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX)

-- Mandatory / Static part of the Query here.
-- Cleartext passwords are verboten, and RTRIM is redundant in filters
SET @SQL = N'SELECT * FROM tUsers WHERE Name = @userID AND PwdHash = @pwdHash'

IF @OptionalParam1 IS NOT NULL
BEGIN
SET @SQL = @SQL + N' AND AnotherField = @OptionalParam1'
END

EXEC sp_executesql @SQL,
N'@userID varchar(20),
@pwdHash varchar(20),
@optionalParam1 NVARCHAR(50)'
,@userID = @userID
,@pwdHash = @pwdHash
,@optionalParam1 = @optionalParam1
END
Go to the top of the page
 
jleach
post May 11 2019, 10:22 PM
Post#2


UtterAccess Editor
Posts: 10,037
Joined: 7-December 09
From: St Augustine, FL


Hi, not quite clear on what you're asking...

Typical calling convention for stored procedures is much like that of a VBA function: the name of the procedure, followed by the values to be passed in:

EXEC MySproc @ThisParam = 'ThatValue', @ThatParam = 'ThisValue'


Or, are you asking why the end of the posted procedure passes the paramters as a string? This is an odd syntax used because of the procedure being called: sp_executesql. That proc isn't really a good example in this case: it takes a string of the SQL to be executed, so the syntax is different than most (generally speaking, any call to sp_executesql should be looked at askance: there's usually a better way to do things than that)

--------------------
Go to the top of the page
 
mrpersonality
post May 11 2019, 11:00 PM
Post#3



Posts: 465
Joined: 4-December 02
From: NSW Australia


I just don't get stored procs I guess
using a pass through query in access passes the parameter values as a comer delimited string-which I thought were the parameters declared at the top of the proc
I am doing this from a search form in an access front end,which can and does have many search criterias-building a string is at least something I can follow,and stored proc seems to be the majority opinion of the best way to do this
----------------
"Or, are you asking why the end of the posted procedure passes the paramters as a string? " yes that's what I am asking
---------------
on reading as much as I can about stored procs on the internet you come across many examples-this example is from a supposed expert,and this example
supposedly stops SQL injection
I will try and find the link-and post it

here it is
http://www.sommarskog.se/dynamic_sql.html
This post has been edited by mrpersonality: May 11 2019, 11:09 PM
Go to the top of the page
 
jleach
post May 12 2019, 10:32 AM
Post#4


UtterAccess Editor
Posts: 10,037
Joined: 7-December 09
From: St Augustine, FL


Erland's reading isn't for the faint of heart: some pretty heavy stuff there (I revisit it once a year or so to thoroughly humble myself). Might find an easier place to start.

Dynamic SQL is dangerous because of potential injection attacks. Any time you build a string, you're open to that, and dynamic SQL is a string executed as SQL. Parameters help prevent that. I think you're in an esoteric corner concerning safer ways to execute dynamic SQL, which is a reasonably advanced topic.

Regarding "regular" stored procedures and parameters, there's two ways to pass them: ordinal or named.

Ordinal is where you just pass them in the order that they appear in the stored procedure's signature line (the one that defines the params):

CODE
    CREATE MySproc @ThisParam NVARCHAR(50), @ThatParam INT AS BEGIN ... END
    GO
    EXEC MySproc 'ThisValue', 50;


Named is where you name them and pass them:

CODE
    CREATE MySproc @ThisParam NVARCHAR(50), @ThatParam INT AS BEGIN ... END
    GO
    EXEC MySproc @ThisParam = 'ThisValue', @ThatParam = 50;


You can do this with local parameters also:
CODE
    CREATE MySproc @ThisParam NVARCHAR(50), @ThatParam INT AS BEGIN ... END
    GO


    DECLARE @MyString NVARCHAR(50) = 'ThisValue'
    DECLARE @MyNumber INT = 50

    EXEC MySproc @MyString, @MyNumber; -- ordinal
    EXEC MySproc @ThisParam = @MyString, @ThatParam = @MyNumber; -- named


hth


[/code]

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 10:34 PM