Stored procedures from Access in MS SQL Server

Question

Stored procedures from Access in MS SQL Server

jaryszek

Default port

Points: 1425
More actions
January 16, 2018 at 12:18 am

#342373

Hi Guys,
In Access i have Query where I am using textboxes fields from form:
SELECT tbl_Slownik.Slownik_ID, tbl_Slownik.PESEL, tbl_Slownik.Person_ID, tbl_Slownik.Nazwisko_imie, tbl_Slownik.Department, tbl_Slownik.Company_NIP, tbl_Slownik.Company_name, tbl_Slownik.Company_shortcut, tbl_Slownik.System, tbl_Slownik.Aktualna
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) AND ((tbl_Slownik.Nazwisko_imie) Like "*" & [Formularze]![tbl_Korekty]![Txb_nazwisko_imie] & "*" Or (tbl_Slownik.Nazwisko_imie) Is Null) AND ((tbl_Slownik.Company_NIP) Like "*" & [Formularze]![tbl_Korekty]![Txb_company_NIP] & "*" Or (tbl_Slownik.Company_NIP) Is Null) AND ((tbl_Slownik.Company_name) Like "*" & [Formularze]![tbl_Korekty]![Txb_company_name] & "*" Or (tbl_Slownik.Company_name) Is Null) AND ((tbl_Slownik.Company_shortcut) Like "*" & [Formularze]![tbl_Korekty]![Txb_company_shortcut] & "*" Or (tbl_Slownik.Company_shortcut) Is Null)) OR ((("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_pesel] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_person_id] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_nazwisko_imie] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_company_NIP] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_company_name] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_company_shortcut] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_pesel] & ""));
I would like to create stored procedure in ms sql server where i could pass one or more parameter like in above query.
I am beginner, please help,
Best Wishes,
Jacek

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jaryszek Default port Points: 1425 More actions · Answer 1

The main problem is how to pass only one variable or 2 variables or more into SP.
And what if my variables will be null ?

Jacek

jaryszek Default port Points: 1425 More actions · Answer 2

Maybe something like this?

CREATE PROCEDURE spDoSearch @FirstName varchar(25) = null, @LastName varchar(25) = null, @Title varchar(25) = null AS BEGIN SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE (@FirstName IS NULL OR FirstName = @FirstName) AND (@LastNameName IS NULL OR LastName = @LastName) AND (@Title IS NULL OR Title = @Title)END