Results 1 to 8 of 8

Stored Procedure in MS SQL Server from Access

  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565

    Stored Procedure in MS SQL Server from Access

    Hello,

    I have a query in MS Access created via query designer.

    Code:
    SELECT tbl_Slownik.Slownik_ID, tbl_Slownik.PESEL, tbl_Slownik.Person_ID
    FROM tbl_Slownik
    WHERE (((tbl_Slownik.PESEL) Like "*" & [Formularze]![tbl_Korekty]![Txb_pesel] & "*" Or (tbl_Slownik.PESEL) Is Null) AND ((tbl_Slownik.Person_ID) Like "*" & [Formularze]![tbl_Korekty]![Txb_person_id] & "*" Or (tbl_Slownik.Person_ID) Is Null)
    I want to create stored procedure in MS SQL Server and later pass variables from Access to SP:

    Code:
    Create Proc spUseParameter  
    @PESEL varchar (20),
    @Person_ID varchar (20)
    AS
    BEGIN
    	SELECT 
    	[UniqueID]
          ,[Data_Zmiany]
          ,[PESEL]
          ,[Person_ID]
          ,[Nazwisko]
          ,[Imie]
          ,[Department]
          ,[Company_NIP]
          ,[Company_name]
          ,[Company_shortcut]
          ,[System_source]
      FROM [SODY].[dbo].[tbl_Slownik]
    	WHERE PESEL Like @PESEL --HERE i have a problem
    END
    
    
    
    
    
    
    spUseParameter '87031604634', ''
    COuld you please help me with writing SQL for this?


    I want to move where clause from Acccess to MS SQL Server.

    BesT wishes,
    Jacek

  2. #2
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    I am almost there:

    Code:
    Create Proc spUseParameter  
    @PESEL varchar (20),
    @Person_ID varchar (20)
    AS
    BEGIN
    	SELECT 
    	   [UniqueID]
          ,[Data_Zmiany]
          ,[PESEL]
          ,[Person_ID]
          ,[Nazwisko]
          ,[Imie]
          ,[Department]
          ,[Company_NIP]
          ,[Company_name]
          ,[Company_shortcut]
          ,[System_source]
      FROM [SODY].[dbo].[tbl_Slownik]
    	WHERE (PESEL Like '%' + @PESEL + '%') AND (Person_ID Like '%' + @Person_ID + '%' or (@Person_ID=null))  --<< the where clause for the parameter(S) must contain the 'place holder'
    END
    
    
    spUseParameter '870316046', null
    But when i am passing null value nothing is selected. But if i write:

    spUseParameter '870316046', ''

    values will be displayed.

    How can i change it?

    Jacek

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,360
    Build the where clause dynamically based on the value of @Person_ID , adding null will result in Null, try
    Code:
    DECLARE @Flibble AS vARCHAR(20)
    
    
    SET  @Flibble = '%' + Null + '%'
    
    
    PRINT @Flibble
    And see the result.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Thank you Minty,

    If user input nothing to textbox Person_Id in Access i want to pass via stored procedure code like here:

    Code:
    SELECT tbl_Slownik.Slownik_ID, tbl_Slownik.PESEL, tbl_Slownik.Person_ID
    FROM tbl_Slownik
    WHERE (((tbl_Slownik.PESEL) Like "*" & [Formularze]![tbl_Korekty]![Txb_pesel] & "*" Or (tbl_Slownik.PESEL) Is Null) AND ((tbl_Slownik.Person_ID) Like "*" & [Formularze]![tbl_Korekty]![Txb_person_id] & "*" Or (tbl_Slownik.Person_ID) Is Null)
    Example:
    User inputs "600" into Txb+Pesel and nothing into Txb_Person_ID.

    So If Txb_Person_ID will be empty on Access form i want to have only PESEL field values filtered containing number "600".

    I was trying to do it using your code in MS SQL Server:
    Code:
    DECLARE @Flibble AS vARCHAR(20)
    SET  @Flibble = '%' + Null + '%'
    PRINT @Flibble
    
    
    Create Proc spUseParameter  
    @PESEL varchar (20),
    @Person_ID varchar (20)
    AS
    BEGIN
    	SELECT 
    	   [UniqueID]
          ,[Data_Zmiany]
          ,[PESEL]
          ,[Person_ID]
          ,[Nazwisko]
          ,[Imie]
          ,[Department]
          ,[Company_NIP]
          ,[Company_name]
          ,[Company_shortcut]
          ,[System_source]
      FROM [SODY].[dbo].[tbl_Slownik]
    	WHERE (PESEL Like '%' + @PESEL + '%') AND (Person_ID Like '%' + @Person_ID + '%' or @Person_ID = @Flibble) 
    END
    
    
    spUseParameter '870316046', Null
    there is an error:

    Code:
    Must declare the scalar variable "@Flibble".
    I know that i have to declare somehow variable @Flibble within my SP.

    BesT wishes,
    Jacek

  5. #5
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,360
    Sorry - If you juts run my code snippet in a new query you'll see you get no result - I was trying to demonstrate Like %Null% will not return any results.
    Your SP will need to look more like this (very basic way of doing it just to demonstrate)
    Code:
    IF @Person_ID is Null
    BEGIN 
        SELECT 
           [UniqueID]
          ,[Data_Zmiany]
          ,[PESEL]
          ,[Person_ID]
          ,[Nazwisko]
          ,[Imie]
          ,[Department]
          ,[Company_NIP]
          ,[Company_name]
          ,[Company_shortcut]
          ,[System_source]
      FROM [SODY].[dbo].[tbl_Slownik]
    
    
      WHERE (PESEL Like '%' + @PESEL + '%') AND (Person_ID is Null)
      END
    ELSE
    BEGIN
            SELECT 
           [UniqueID]
          ,[Data_Zmiany]
          ,[PESEL]
          ,[Person_ID]
          ,[Nazwisko]
          ,[Imie]
          ,[Department]
          ,[Company_NIP]
          ,[Company_name]
          ,[Company_shortcut]
          ,[System_source]
      FROM [SODY].[dbo].[tbl_Slownik]
    
    
      WHERE (PESEL Like '%' + @PESEL + '%') AND (Person_ID Like '%' + @Person_ID + '%')
    
    
    END
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Thank you Minty!

    A lot of code here.
    I think that there should be better way of doing it, i am trying with:

    Code:
    Create Proc spUseParameter  
    @PESEL varchar (20),
    @Person_ID varchar (20),
    --@Nazwisko_Imie varchar (20),
    @Company_NIP varchar (20),
    @Company_name varchar (20),
    @Company_shortcut varchar (20)
    AS
    BEGIN
    	SELECT 
    	   [UniqueID]
          ,[Data_Zmiany]
          ,[PESEL]
          ,[Person_ID]
          ,[Nazwisko]
          ,[Imie]
          ,[Department]
          ,[Company_NIP]
          ,[Company_name]
          ,[Company_shortcut]
          ,[System_source]
      FROM [SODY].[dbo].[tbl_Slownik]
    	WHERE (PESEL Like '%' + @PESEL + '%' or @PESEL = '%' + Null + '%') 
    	AND (Person_ID Like '%' + @Person_ID + '%' or @Person_ID = '%' + Null + '%') 
    	--AND (Nazwisko_Imie Like '%' + @Nazwisko_Imie + '%' or @Nazwisko_Imie is Null) 
    	AND (Company_NIP Like '%' + @Company_NIP + '%' or @Company_NIP = '%' + Null + '%') 
    	AND (Company_name Like '%' + @Company_name + '%' or @Company_name = '%' + Null + '%') 
    	AND (Company_shortcut Like '%' + @Company_shortcut + '%' or @Company_shortcut = '%' + Null + '%') 
    END
    And now:

    Code:
    spUseParameter '87031604634', Null,Null, Null, Null
    Working!
    Code:
    spUseParameter '87061604634','','','',''
    Not working !

    Jacek

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,360
    Sorry you are missing my point - I was demonstrating to you that adding Null to anything results in Null, so
    ' %' + Null + '%'
    Isn't achieving anything. It is Null.

    The use of '' is not the same as Null either. '' is a zero length string (ZLS) , it is NOT Null. They are very different.

    The simple route would be to set your criteria before the select. Something like
    Code:
    Create Proc spUseParameter  
    @PESEL varchar (20),
    @Person_ID varchar (20),
    @Company_NIP varchar (20),
    @Company_name varchar (20),
    @Company_shortcut varchar (20)
    AS
    
    
    DECLARE
    @ssPESEL varchar (20),
    @ssPerson_ID varchar (20),
    @ssCompany_NIP varchar (20),
    @ssCompany_name varchar (20),
    @ssCompany_shortcut varchar (20)
    
    
    SET @ssPESEL = Case WHEN @PESEL IS Null Then '%' ELSE  '%' + @PESEL + '%' END
    SET @ssPerson_ID = Case WHEN @Person_ID IS Null Then '%' ELSE  '%' + @Person_ID + '%' END
    SET @ssCompany_NIP = Case WHEN @Company_NIP IS Null Then '%' ELSE  '%' + @Company_NIP + '%' END
    SET @ssCompany_name = Case WHEN @Company_name IS Null Then '%' ELSE  '%' + @Company_name + '%' END
    SET @ssCompany_shortcut = Case WHEN @Company_shortcut IS Null Then '%' ELSE  '%' + @Company_shortcut + '%' END
    
    
    
    
    BEGIN
        SELECT 
           [UniqueID]
          ,[Data_Zmiany]
          ,[PESEL]
          ,[Person_ID]
          ,[Nazwisko]
          ,[Imie]
          ,[Department]
          ,[Company_NIP]
          ,[Company_name]
          ,[Company_shortcut]
          ,[System_source]
        FROM [SODY].[dbo].[tbl_Slownik]
        WHERE (PESEL Like @ssPESEL) 
        AND (Person_ID Like @ssPerson_ID) 
        AND (Company_NIP Like @ssCompany_NIP) 
        AND (Company_name Like @ssCompany_name) 
        AND (Company_shortcut Like @ssCompany_shortcut) 
    END


    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Wow Minty YOu are a master !

    Thank you very much !

    It is working like a charm
    Jacek

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass Text Box Value To SQL Server Stored Procedure
    By Juan4412 in forum Programming
    Replies: 1
    Last Post: 04-11-2017, 06:58 AM
  2. Replies: 2
    Last Post: 06-01-2016, 08:52 AM
  3. Stored Procedure in MS Access 2007
    By sels1987 in forum Access
    Replies: 1
    Last Post: 05-13-2012, 12:23 PM
  4. Replies: 3
    Last Post: 03-20-2012, 10:00 AM
  5. Replies: 3
    Last Post: 05-23-2010, 05:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums