Building Dynamic Sql in Stored Proc issue

Hi all,

I'm gonna need some help with this one.

I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:

SELECT N_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROM dbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ............and so on

What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.

So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this? Help!!

ThanksYou can store your parameters into a 15-column temporary table and then join the main query with it.|||Alternativley...use and exec statemnt ( not for the weak of stomach )

declare @.var_1 varch(10), @.var_2 varchar (10)
set @.var_1 = 'value_1'
set @.var_2 = 'value_2'

exec( ' select * from table where x = ' +@.var_1+ ' and y = ' +@.var_2+ '')

so :

exec('..............
Where upper(title) like %'+@.parameter_value'%
and company_id = '@.parameter_value'
and Nmt_practice_code = '@.parameter_value'')

You effectively have to enclose the whole statement in an exec statement.
Its messy this way, and you will take a while to debig to get it right.

rdjabarov has a good idea.....its more elegant than my approach....

Building a mailing list

Hi folks,

I'm building a DTS package which needs to mail a list of users nightly

The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.

I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.

The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3...etc).

I'm a bit unsure on how to go about doing / writing this.

Can anyone help?

Thanks,

Davethis would be far easier if you trashed the dts, built a stored procedure and then created a job to run the procedure.|||Thought about that but my Stored Proc skills are nothing to write home about. If you have anything I could tweak to suit my needs I'd be grateful.

Thanks,

Dave|||Try running this sql through Query Analyzer:declare @.MyEmailString varchar(8000)

select @.MyEmailString = coalesce(@.MyEmailString + ',', '') + coalesce(email, '')
from employees
where role = 'MGR'

select @.MyEmailString|||Thanks Blindman.

Still trying to get my head around the syntax and use of Coalesce but it works.

Thanks again,

Dave|||Hi Blindman,

I need to run the code below against a MySql database.
It works fine when I modify it for an MS SQL db however I'm getting syntax errors when running it against a MySQL (5.1.9) db.

Any ideas?

Thanks,

Dave|||I wouldn't be surprised if this syntax didn't work in MySQL. Try posting your question in the MySQL forum.|||Lord, where's R937 when somebody mentions his favorite toy! You probably want the GROUP_CONCAT (http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html) function in MySQL.

<Afterthought>
After posting this, I realized that Rudy would probably pitch a fit, mostly because he enjoy's pitching fits. Note that I do NOT consider MySQL to be a toy, Rudy just likes to play with it because MySQL offers functions that violate the basic rules of relational algebra (such as the GROUP_CONCAT() function). This violation of hte rules doesn't make MySQL a bad product in any way, although I see it as removing MySQL from the category of Relational databases since it doesn't follow the basic rules required for a relational database.

-PatP

Build SQL query from dynamic Checkbox List

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.There are 101 ways to skin this cat. And there are a few parts to it. I'm not sure which part you're looking for the help, but since you've posted this in the SQL forum, I'm going to assume you have the asp side of it down.

Some thoughts and assumptions first.

Assumptions
1) You don't have too many locations for each user. (hopefully less than a hundred)
2) You can have 0 to many locations per user.
3) There is a user table and a location table (exact definition is up to you)
4) You know how to loop through arrays in your web code

Thoughts
1) You probably will want to query on which users belong to a particular location
2) You probably want to have a query return which locations belong to particular user (not just from this particular checkboxed web page either)
3) You will want to have the retrieval and update happen in a single connection across the wire from web server to db server.

With the above thoughts, it eliminates the concept of storing the list of locations as a delimited list in a field. It's too hard to query, search, index, join, etc. That leaves you with creating a many to many join table such as:
Table Name: CustomerLocation
Fields:
CustomerID (PK)
LocationID (PK)

The trick you now have to figure out is how to get the array of id's from the web page into a normalized table such as this, and vice versa.

If you had a stored procedure that basically took the input of CustomerID and a delimited list of LocationID's, you could then loop through the list, dynamically create insert statements into the relationship table. For an example, look at http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
This article is using the array as primarily a where clause, but if you used the Method 1 to create an insert statement (with appropriate handling for when the values already exist) then you could accomplish your insert/update/delete scenarios.

For retrieval there's a sneaky way to get a delimited list of values. See this article and look basically at the last sql statement concept.
http://www.sqlteam.com/item.asp?ItemID=2368

David

Build Dynamic Table Columns Issue

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!
J827use a hughe varchar variable and fill it with a create table statement. To determine which columnnames, try information_schema.tables. Then execute it using exec.|||You need this

http://www.sqlteam.com/item.asp?ItemID=2955|||Brett,

Thanks for the Link and it works for my case.

J827|||Hey, thank Rob Volk...he's the author...

I'm just the messenger...

Lots of good articles over there...

Good Luck

Build Dynamic Query Using sp_executesql

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @.PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @.SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@.PracticeCode+' SELECT
SurgeryKey,'+
@.extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @.SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

ThanksManaged to figure it out all by myself. Think I was a bit premature in posting this one...sorry :-)

Here's my solution in case anyone is intertested:

DECLARE @.SQLString nvarchar(500)
DECLARE @.Parameters nvarchar(500)


SET @.Parameters = '@.Date DateTime'
-- insert data into proper tables with extract date added
SET @.SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@.PracticeCode+' SELECT
SurgeryKey,
@.Date,
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql@.SQLString,@.Parameters,@.Date= @.ExtractDate