Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts

Friday, March 23, 2012

Looping in a stored proceedure

What I would like to be able to do but am not sure if I can is the following
.
I need to set a variable = to the results of a single field recordset:
@.X = select EmployeeID from Employees where DepartmentID = 1
Then I need to build a dynamic sql statement based on the above results.
If the first recordset has three records. I need to loop through it three
times and concatinate the results of the sql that would look like this:
This or something like it would be in the loop...
@.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
END RESULT: @.SQL would now look like this
select * from customers where EmpID = 1, select * from customers where EmpID
= 2, select * from customers where EmpID = 3
The text between the comma's can increase or decrease depending on the
number of records in the first sql statement at the top of the page.
Any thoughts
Thank you
KentHi Kent.
You could try:
select ' select * from customers where EmpID=' + EmployeeID from
Employees where DepartmentID = 1
Bryce|||Why not just:
SELECT *
FROM Customers
WHERE empid IN
(SELECT employeeid
FROM Employees
WHERE departmentid = 1)
Dynamic SQL is bad news in a production application. Also, avoid SELECT
*. Code is safer, easier to maintain and maybe more efficient if you
list just the required column names.
David Portas
SQL Server MVP
--|||Kent,
What is the reason of doing this?
why not:
select employeeid, departmentid, ...
from employees
where departmentid = 1
AMB
"Kent Prokopy" wrote:

> What I would like to be able to do but am not sure if I can is the followi
ng.
> I need to set a variable = to the results of a single field recordset:
> @.X = select EmployeeID from Employees where DepartmentID = 1
> Then I need to build a dynamic sql statement based on the above results.
> If the first recordset has three records. I need to loop through it three
> times and concatinate the results of the sql that would look like this:
> This or something like it would be in the loop...
> @.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
>
> END RESULT: @.SQL would now look like this
> select * from customers where EmpID = 1, select * from customers where Emp
ID
> = 2, select * from customers where EmpID = 3
> The text between the comma's can increase or decrease depending on the
> number of records in the first sql statement at the top of the page.
> Any thoughts
> Thank you
> Kent|||I need to build an Excel report that has each Department on a diferant sheet
.
On each sheet will be a column for each employee. The number of column will
vary depending on the number of employee's in each department. So if
Department Dep1 has 5 employee's the sheet will have five columns. I could
populate each column one at a time, but would like to be able to do this
dynamicly. If posable.
"bd" wrote:

> Hi Kent.
> You could try:
> select ' select * from customers where EmpID=' + EmployeeID from
> Employees where DepartmentID = 1
> Bryce
>|||Correction,
select c.*
from customers as c inner join employees as e
on c.empid = e.employeeid and e.departmentid = 1
AMB
"Alejandro Mesa" wrote:
> Kent,
> What is the reason of doing this?
> why not:
> select employeeid, departmentid, ...
> from employees
> where departmentid = 1
>
> AMB
>
> "Kent Prokopy" wrote:
>|||My bad. Sorry I do not need * from... I need
For each employee I need a column/field.
select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
This will give me three columns in an Excel report. or two columns or XXXXX
"David Portas" wrote:

> Why not just:
> SELECT *
> FROM Customers
> WHERE empid IN
> (SELECT employeeid
> FROM Employees
> WHERE departmentid = 1)
> Dynamic SQL is bad news in a production application. Also, avoid SELECT
> *. Code is safer, easier to maintain and maybe more efficient if you
> list just the required column names.
> --
> David Portas
> SQL Server MVP
> --
>|||select EmpID , count(*) from DataTable
where DataDate = yesterday
GROUP BY EmpID
and do the pivoting in Excel.
Jacco Schalkwijk
SQL Server MVP
"Kent Prokopy" <KentProkopy@.discussions.microsoft.com> wrote in message
news:2126E40D-33DA-4389-A865-73E33F716A0D@.microsoft.com...
> My bad. Sorry I do not need * from... I need
> For each employee I need a column/field.
> select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
> This will give me three columns in an Excel report. or two columns or
> XXXXX
> "David Portas" wrote:
>|||Thank you all for your help/thoughts.
I have come up with a solution that will work.
I am going to build the sql statement in vb code and pass it tp the SP as a
varchar.
"David Portas" wrote:

> Use an Excel Pivot Table for that. You can query the database directly
> and it will create the columns for you. Alternatively you could use
> DTS.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have come up with a solution that will work.
> I am going to build the sql statement in vb code and pass it tp the SP as
a
> varchar.
Ugh, WHY? Sure, that will *work* but it is far and away from the best
solution. This is like going to the grocery store with a list of bar codes
for the products you want to buy.

loop through tables for UNION ALL

Hey folks,
I have a variable number of identically structured & similarly named
tables for which I would like to expeditiously execute a UNION ALL in
succession. e.g. "SELECT * from (x-2) UNION ALL SELECT * FROM (x-1) UNION
ALL SELECT * FROM x UNION ALL" etc... where (x-2), (x-1), x are three such
tables. I can pull all the tables I need to create this very large UNION ALL
query from INFORMATION_SCHEMA.TABLES, so is there any way to somehow
construct a looped stored procedure such that I don't have to manually type
in the full UNION ALL query?
Thanks,
Dave
--
______________________________
Remove "_SPAM" to reply directly.David Shorthouse wrote:
> Hey folks,
> I have a variable number of identically structured & similarly named
> tables
Why?

> for which I would like to expeditiously execute a UNION ALL in
> succession. e.g. "SELECT * from (x-2) UNION ALL SELECT * FROM (x-1) UNION
> ALL SELECT * FROM x UNION ALL" etc... where (x-2), (x-1), x are three such
> tables. I can pull all the tables I need to create this very large UNION A
LL
> query from INFORMATION_SCHEMA.TABLES, so is there any way to somehow
> construct a looped stored procedure such that I don't have to manually typ
e
> in the full UNION ALL query?
>
Create a UNION by querying the INFORMATION_SCHEMA and then cut and
paste the result. Put the UNION in a view and query that in your proc.
Take a look at the partitioned views topic in Books Online.
Better still, consider combining this into a single table unless it's a
case of "I'm not allowed to fix my database".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I'll take it as read that performance is not a problem here as if it were
the driving factor a DB redesign would not be out of the question.
Insead of trying to perform a union all, maybe a different appear would be
better...
1. Create a temporary table with the structure of your output.
2. create a cursor which will loop through the tablenames that you need to
query.
3. use the table name to create a dynamic sql statement (see sp_executesql)
that will populate the temporary table with the data that you need to
select.
4. after the loop, perform a single select on the temp table.
Be warned, the performance of this will be extremely bad.
Regards
Colin Dawson
www.cjdawson.com
"David Shorthouse" <davidshorthouse@.shaw_SPAM.ca> wrote in message
news:uX5XwbqPGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have a variable number of identically structured & similarly named
> tables for which I would like to expeditiously execute a UNION ALL in
> succession. e.g. "SELECT * from (x-2) UNION ALL SELECT * FROM (x-1) UNION
> ALL SELECT * FROM x UNION ALL" etc... where (x-2), (x-1), x are three such
> tables. I can pull all the tables I need to create this very large UNION
> ALL query from INFORMATION_SCHEMA.TABLES, so is there any way to somehow
> construct a looped stored procedure such that I don't have to manually
> type in the full UNION ALL query?
> Thanks,
> Dave
> --
> ______________________________
> Remove "_SPAM" to reply directly.
>|||
> Why?
This is simply the way the db has been designed. Individual clients access
individual tables.

> Create a UNION by querying the INFORMATION_SCHEMA and then cut and
> paste the result. Put the UNION in a view and query that in your proc.
> Take a look at the partitioned views topic in Books Online.
> Better still, consider combining this into a single table unless it's a
> case of "I'm not allowed to fix my database".
This is what I am attempting to do; scheduling a table creation by creating
a UNION ALL of these individual tables. Rather than having to manually add
these tables to the UNION ALL query prior to the creation of the one large
result, I am attempting to programmatically create this UNION ALL because
there are a variable number of individual tables. So, I'm not sure how the
idea you have above will accomplish this.
Dave|||
> Insead of trying to perform a union all, maybe a different appear would be
> better...
> 1. Create a temporary table with the structure of your output.
> 2. create a cursor which will loop through the tablenames that you need to
> query.
> 3. use the table name to create a dynamic sql statement (see
> sp_executesql) that will populate the temporary table with the data that
> you need to select.
> 4. after the loop, perform a single select on the temp table.
> Be warned, the performance of this will be extremely bad.
Colin,
Thanks for the ideas. Performance is not an issue here because I am indeed
creating a table from this large UNION ALL query result.|||>> This is simply the way the db has been designed. Individual clients acces
s individual tables. <<
Well, kill the moron who did this and fix it!! The name of this design
flaw is "attribute splitting" and it can take several forms.
1) Putting the same set of entities into many tables. There are two
sub-cases.
a.The absolute newbie usually mimics sequential numbered or
temporally labeled tapes in each table.
b. The newbie with a few ws experience splits the table on the
values of one or more attributes. For example, taking a Personnel
table and making it into "MalePersonnel" and "FemalePersonnel" tables.
2) Taking a single attribute and splitting in into multiple columns.
There are two sub-cases.
a. The multiple columns are in the same table. The most common
example is having a group of bit flag columns instead of a single
well-design encoding in a column. They have to be combined to get the
attribute's value and you need multi-column CHECK() constraints to
control the combinations.
b. The split column is in multiple tables. The most common example
is trying to force an OO design in an RDBMS, so that joins are required
to assemble a meaningful entity. . They have to be combined via a join
to get the attribute's value.|||As David has suggested, look at partitioned views. You can create a
single entity (a view) that contains all of the data from your tables.
Through the use of check constraints, the optimizer can do partition
exclusion, which means it will only select data from the appropriate
underlying tables. The VIEW is also able to be UPDATED, so you end up
dealing with a lot less mess than trying to dynamically select the
appropriate tables during your selection time.
Of course, the only real reason for using a partioned view is to help
performance when dealing with large datasets; in your case, it sounds
like you're having to do an end-run around a design choice that should
be reconsidered.
Stu

Wednesday, March 21, 2012

loop through certain files

Using SSIS foreach loop, I am looping through files to get the filenames and pass them on as variable.
How is it possible to loop through the files but only pass the files that do not end with ..._Parameters.xml? or loop through only the ones which end with ..._Parameters.xml
I think this is to do with the scripting or expression in the foreachloop?
ThanksYou could use a script task to determine which path to take (i.e. is _Parameters.xml or isNot _Paremeters.xml). One way you could do this would be by using the "Fail" path if the task if it is _Parameters.xml, and otherwise use the sucess path. I'm not sure if this is the best way to go about this or not though... (You would need to make sure that this task would not cause the container to fail when it does)|||Can't you set the files property (available in the dialog) to "*_Parameters.xml" to get the ones that do match?|||

Good thinking.

Done that now.

Thanks

sql

Monday, March 19, 2012

Lookup using NText variable

Hi Guys,

Is there anyway to do a lookup for ntext variables?

Thanks.No, LOB columns (TEXT, NTEXT, IMAGE) are not available for lookup. If you can convert to a STR or WSTR (both in IS and in the reference table) then you could do the lookup.

Lookup Transform with Variable Parameter

Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.

Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?

Thanks!

Brandon

Brandon I don't believe this can be done with the Lookup Transform, as I have ran into this limitation before.
Adrian
|||

The way I did it was to use a derived column transform before the lookup transform that "transform" my variable in a column. By doing this, I can now see the new column in the input column of the "set query parametsrs" parameter window.

Ccote

Lookup task based on a variable

This doesn't seem possible but I'll ask anyway...

Can I build a lookup task where the lookup query is based on a variable, rather than hardcoding the SQL staement?

No, there is no support for this.

The best you can do is write a query and then use values in your input data as parameters, to help filter the rows. Seems obvious, but often I have got stuck thinking I wanted a parameterised query, so that results are filtered before I do the lookup, which cannot be done. This is probably not as effcient, but the closest we have.

|||

That's what I figured...

On the bright side, I was able to use another new SQL Server 2005 feature...

SELECT RecId, CAST(COUNT(*) AS Char(1)) [PVL]
FROM VT
WHERE Elect IN (

SELECT Elect
FROM (
SELECT State,PVLE1,PVLE2,PVLE3,PVLE4
FROM StateOptions) opt
UNPIVOT
(Elect For State IN (PVL1, PVL2, PVL3, PVL4)) AS UPV

)

GROUP BY RecId

Of course this would have been much simpler if the options table had been built vertically instead of horizontally!