Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
--
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> > Is there a view or something that lets you see what recovery mode your
> > db is set to?
> >
> > I'm trying to tidy up a stored procedure that does a backup of all the
> > trans logs, but I get an error when it hits a db set to Simple.
> >
> > Thx.
> >

Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:[vbcol=seagreen]
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...

Easy stored procedure

Hi,
I have two tables...
Employees Departments
| EmployeeID | DeptID
| Name | Name
| Department |
|______________ | _____________
I would like to know how could i make an insert procedure on employees, inse
rting in the employeeid the next numeric value (in the table), and take in c
ount that the name of the employee must dont exists ...
Thanks and Regards.
Any Help will be grateful, urls, articles, anything...
Josema.See my reply in .programming
Vishal Parkar
vgparkar@.yahoo.co.in

Dynamicly create report

Hi All,
I would like to create report like below:
For example: I have a stored procedure spDeptEmp, which has a Dept ID
as input parameter, Once the Dept ID has been passed in, I will get
all the employees on that dept. The question is: I would like to
generate the report, which will display each employee's detail
information, one person per page.
Could you let me know how can I do that using reporting services?
Thanks in advance!
--BillWhat do you mean by dynamic? I don't see the report being dynamic (i.e. that
you show different columns at different times or some such thing). It seems
to me that you are just returning different data depending on the parameter
but the format/layout etc of the report is unchanged. Everything you
describe here is very vanilla report generation for RS. You can easily add
page breaks, you can easily have a query based on a parameter.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408250950.723ae518@.posting.google.com...
> Hi All,
> I would like to create report like below:
> For example: I have a stored procedure spDeptEmp, which has a Dept ID
> as input parameter, Once the Dept ID has been passed in, I will get
> all the employees on that dept. The question is: I would like to
> generate the report, which will display each employee's detail
> information, one person per page.
> Could you let me know how can I do that using reporting services?
> Thanks in advance!
> --Bill|||"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> What do you mean by dynamic? I don't see the report being dynamic (i.e. that
> you show different columns at different times or some such thing). It seems
> to me that you are just returning different data depending on the parameter
> but the format/layout etc of the report is unchanged. Everything you
> describe here is very vanilla report generation for RS. You can easily add
> page breaks, you can easily have a query based on a parameter.
> Bruce L-C
> "bill" <bli2001@.hotmail.com> wrote in message
> news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > Hi All,
> >
> > I would like to create report like below:
> > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > as input parameter, Once the Dept ID has been passed in, I will get
> > all the employees on that dept. The question is: I would like to
> > generate the report, which will display each employee's detail
> > information, one person per page.
> >
> > Could you let me know how can I do that using reporting services?
> >
> > Thanks in advance!
> >
> > --Bill
The dynamic means you don't know how many employees inside one dept.
until you get the input parameter(dept ID). Different dept. will have
different number of employees. i.e. the report will be different.
Also, for one employee's information, it will come from different
dataset.
Thanks,
--Bill|||What you are wanting to do is exactly what RS is designed to do quite
easily. If a simple matter of here is a dept, list all employee's
information with page breaks between them. That would be a single
parameterized query with appropriate grouping and page breaks. If it is more
a master detail type report then subreports will do what you want.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408251442.232899ab@.posting.google.com...
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> > What do you mean by dynamic? I don't see the report being dynamic (i.e.
that
> > you show different columns at different times or some such thing). It
seems
> > to me that you are just returning different data depending on the
parameter
> > but the format/layout etc of the report is unchanged. Everything you
> > describe here is very vanilla report generation for RS. You can easily
add
> > page breaks, you can easily have a query based on a parameter.
> >
> > Bruce L-C
> >
> > "bill" <bli2001@.hotmail.com> wrote in message
> > news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > > Hi All,
> > >
> > > I would like to create report like below:
> > > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > > as input parameter, Once the Dept ID has been passed in, I will get
> > > all the employees on that dept. The question is: I would like to
> > > generate the report, which will display each employee's detail
> > > information, one person per page.
> > >
> > > Could you let me know how can I do that using reporting services?
> > >
> > > Thanks in advance!
> > >
> > > --Bill
> The dynamic means you don't know how many employees inside one dept.
> until you get the input parameter(dept ID). Different dept. will have
> different number of employees. i.e. the report will be different.
> Also, for one employee's information, it will come from different
> dataset.
> Thanks,
> --Bill

Dynamically substitute table name in Cursor Defn.

hi,
I am looking for a soln,where in I can substiute the name of the table
dynamically with a paramter passed through the procedure in the Cursor
Defn
as shown below.

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

CURSOR TCN_cur IS

SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM
p_table_name py;

here note the p_table_name used for cursor declaration(is passed as a
parameter to the proc)...
Any soln or any other way of implemention this is appreciated.

Rgds
VivianYou need to use dynamic SQL:

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

TYPE refcur IS REF CURSOR;
tcn_cur refcur;

BEGIN

OPEN tcn_cur FOR
'SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM '||p_table_name||' py';
...

By the way, I'm not sure this does what you intend:

error_msg VARCHAR2(300) := SQLERRM;

The value of SQLERRM will be assigned at the time the variable is declared - but there hasn't been any error yet, so the value will always be "ORA-0000: normal, successful completion". Or maybe that's the default value you want if no error occurs?|||Hi,
Thks for ur reply.

I want this cursor fo looping record by record.How do I do that. eg

CREATE OR REPLACE procedure test (tab_id in integer)

TYPE refcur IS REF CURSOR;
c_Data refcur;


begin

select table_name into stg_table from table_source where table_type='ST' and table_id=tab_id and src_id=2;

open c_Data for 'Select * from '||stg_table||'order by record_id;';
loop
fetch c__data into rec_id ;
EXIT WHEN c_gib_data%NOTFOUND;
----
---- /* Some actions
end Loop;

When I complie,I get a error at the cursor where the sql statment is created

BEGIN test_dym(1); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "USR.TEST", line 46
ORA-06512: at line 1|||1) You need a space before 'order'
2) You should not put a semi-colon in the SQL string

open c_Data for 'Select * from '||stg_table||' order by record_id';|||Thnks Andrew.I t worked.|||Hi,
Suppose if I have to do the follwoing,how do I implment using REF cursor.Actually I need to pass a IN paramter to the cursor. Can this be implemeted in REF cursor

CURSOR c_Data( P_Serial_Number IN VARCHAR2 ) IS
SELECT
record_id,record_history_status ,updation_ico_count
FROM
prod_data
WHERE
record_id = ( SELECT max(record_id)
FROM
prod_data
WHERE
Serial_number LIKE P_Serial_Number);|||You don't pass a parameter to the ref cursor, instead you use a BIND VARIABLE and the USING clause. Here is a simple example:

DECLARE
TYPE refcur IS REF CURSOR;
c refcur;
r dept%ROWTYPE;
BEGIN
OPEN c FOR 'SELECT * FROM dept WHERE deptno > :mindeptno' USING 10;
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(r.deptno);
END LOOP;
CLOSE c;
END;
/

In this example, the value 10 is used for the bind variable :mindeptno in the query. In your example this would be used like this:

OPEN c_data FOR 'SELECT ... WHERE Serial_number LIKE :x' USING P_Serial_Number;|||Hi,
I have defined the cursor as ref cursor as follows

TYPE c_Data_refcur IS REF CURSOR; --Cursor with dynamic table defn
c_Data c_data_refcur;

TYPE c_View_Data_refcur IS REF CURSOR; --Cursor with Dynamic
table defn and IN Para
c_View_Data c_View_Data_refcur;

Begin

--The value for stg_table is there with me --
open c_Data for'Select * from '||stg_table||' order by record_id';
loop
fetch c_Data into rec_id ; (Q1 : rec id should be deifned as what %rowtype as the table name is generated dynamically)

EXIT WHEN c_data%NOTFOUND;

IF v_transformation_boolean = FALSE
THEN
SELECT Transfer_ID_Seq.NEXTVAL INTO v_TransferSeq FROM dual;
v_start_record_id := stg_table.record_id; (Q2: How do I specify the fetch cursor value here?)
v_transformation_boolean := TRUE;
END IF;

v_end_record_id := stg_table.record_id; (Q3 How do I specify the fetch cursor value here?)

Q4 : Here I have to inialize the field values as null for the second cursor which has a In paramter.Here again the table name is dynamically populated for the cursor defn.)
c_GibView_Data.updation_ico_count := NULL;
c_GibView_Data.record_history_status := NULL;
c_GibView_Data.ico_status:= NULL;

--2 cursor having IN Parameter--
OPEN c_View_Data for 'SELECT record_id, record_history_status ,updation_count, status FROM'||prd_table||'WHERE record_id = ( SELECT max(record_id) FROM '||prd_table||'WHERE Serial_number = :P_Serial_Number)' using c_Data.serial_number;(This defn is not allowed)
FETCH c_View_Data INTO {c_GibView_Data_Rec};(Q5: how do i defin this as %row type as the table is dynamically populated)
CLOSE c_View_Data;|||Q1) You cannot declare a variable dynamically using NDS, you would need to know the structure of the record in advance. You need to use DBMS_SQL to handle this situation.
Q2) If you had a record declared like v_rec mytable%ROWTYPE into which you had fetched a row, then you would say v_rec.record_id
Q3) Same as Q2
Q4) Don't understand the question.

Dynamically specify server and database in Stored Procedure

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')?)

Thanks for any help

Hi,

that is not (yet) parameterizable. You would have to use dynamic sql here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Dynamically select tables

Hi,
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:

> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet

Dynamically execute stored procedure

Hello,
I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:
insert into Table1
(
id, name
)
select id, name
from Table2
Can you do something like:
exec spProc @.id = id, @.name = name
from Table1
Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.
Thanks.

You can create a SQL cursor base on Table 1. Then iterate over the cursor and build your SQL statements dynamically using that syntax of the Execute statement. You can find syntax in SQL Books Online.

HTH.

dynamically creating a select statement

I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:

ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END

Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.

There's two approaches that I can think of that might help you.

Firstly, it's entirely valid to include criteria such as

WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...

in the stored procedure.

However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:

declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)

set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''

if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end

if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end

... and so on for the other parameters ...

set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end

exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than

Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).

Let me know if you need any further assistance with this.

Iain

|||

You can try this:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SearchCurrency]

@.currencycode nchar(5) = null,

@.currencyname nchar(25) = null

as

begin

declare @.stmt nvarchar(max)

set @.stmt = 'SELECT currencycode, currencyname

FROM currencies where 1=1 '

IF @.currencycode IS NOT NULL

set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''

IF @.currencyname IS NOT NULL

set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''

exec(@.stmt)

end

|||

I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.

See:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Dynamically create SQL Scripts

I would like to be able to create SQL Scripts with a stored procedure (or some other means). I have looked all through the help files and even tried watching the profiler to see how EM does it. Any help would be appreciated.I've written a set of procedures that will generate INSERT/SELECT/UPDATE statements for a given table, plus more. Is that what you are looking for?|||Thanks for your reply.

I went back and read my post and I'm sorry, I should have been more clear. I am looking for a way to dynamically generate the scripts to create the objects themselves (Stored Procedures, Tables, Logins, etc.). I would like to have a job that would fire every week and write out the individual scripts for each object into a directory named the same as the database. Basically, I want to automate the "Generate SQL Scripts" menu option in EM.|||Don't know an easy way to do this, I don't think you can do it using DTS
at least not pre-2000.

The hard way is to write an SP which generates all the DDL using
sysobjects, syscolumns, systypes, sysindexes, syslogins, sp_helptext etc.

You can use sp_help code as a preliminary guide on how to extract the DDL.|||You can use SQLDMO to generate the script. What version of SQL Server are you using, I may have a VB program that does this and a Perl program.|||Thanks for the reply plus any help given, I am using 2000.|||achorozy, you mentioned I could use DMO (something I have zero experience with) or you might have something I can use. Can you help me?

Thanks|||I've attached a VB project that was written using SQLDMO to generate SQL script for a given database.

This script was originally for 6.5 but was converted to 7.0 and 2000 by changing the SQLDMO libray from SQLOLE to SQLDMO. I believe this code original came from an example on DEVX a few years ago. This is not my code but I did modify it to work with 7.0 and 2000.|||Attachment|||Thanks!!

That worked great.

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...
Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = GETDATE(); #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Try this ...
DECLARE @.tname VARCHAR(30), @.sql VARCHAR(2000)
SET @.tname = CONVERT(CHAR(8),GETDATE(),112)-- remove the spaces
SELECT @.sql = 'CREATE TABLE dbo.##' + @.tname + ' (mycol CHAR(10))' -- needs
to be global or permenant
SELECT @.sql
EXECUTE (@.sql)
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = GETDATE(); #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>|||Why would you need dynamically named and/or dynamically created objects? How
are you planning on using them?
This is possible, but hardly recommended. There are better options and if
you post more information we can help you design a much more efficient
solution.
But if you insist on creating SQL objects dynamically you should read this
great article on dynamic SQL by Erland Sommarskog:
http://www.sommarskog.se/dynamic_sql.html
ML
http://milambda.blogspot.com/

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:
> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamic XML using for XML explicit

Hello everyone,
I had written a procedure to generate the XML in which the xml
structure was static.
eg.
<root>
<parent1>
<child></child>
</parent1>
<parent2>
<child></child>
</parent2>
</root>
for this i wrote two select queries and using UNION ALL and FOR XML explicit
generated it.
now the structure of XML to be generated is dynamic depending on the
parameter that is passes to the storedprocedure.
so the xml cud be <root><parent><subparent><child><subchild> for one
parameter
and
<root><child>
for another and <root><parent><child>
for other so it wud keep changing.
My question is how should i write the procedure to generate dynamic XML.
i can know the no of levels as soon as the parameter os given.
i'd be glad if somebody could tell me an approach to generate dynamic XML.
thanks and regards,
toby
Hello Toby,
If you only have those three possibilities to worry about, consider using
a T-SQL case based on the parameter value, then using having a clause that
executes the correct FOR XML EXPLICIT query as part of that. Otherwise, you're
asking for basically non-maintainable code.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||And if you use SQL Server 2005, you can use nested FOR XML PATH expressions
to make the code more modular and maintainable...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74ed968c7ba38196ca340@.news.microsoft.com ...
> Hello Toby,
> If you only have those three possibilities to worry about, consider using
> a T-SQL case based on the parameter value, then using having a clause that
> executes the correct FOR XML EXPLICIT query as part of that. Otherwise,
> you're asking for basically non-maintainable code.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

dynamic XML using for XML explicit

Hello everyone,
I had written a procedure to generate the XML in which the xml
structure was static.
eg.
<root>
<parent1>
<child></child>
</parent1>
<parent2>
<child></child>
</parent2>
</root>
for this i wrote two select queries and using UNION ALL and FOR XML explicit
generated it.
now the structure of XML to be generated is dynamic depending on the
parameter that is passes to the storedprocedure.
so the xml cud be <root><parent><subparent><child><subchild> for one
parameter
and
<root><child>
for another and <root><parent><child>
for other so it wud keep changing.
My question is how should i write the procedure to generate dynamic XML.
i can know the no of levels as soon as the parameter os given.
i'd be glad if somebody could tell me an approach to generate dynamic XML.
thanks and regards,
tobyHello Toby,
If you only have those three possibilities to worry about, consider using
a T-SQL case based on the parameter value, then using having a clause that
executes the correct FOR XML EXPLICIT query as part of that. Otherwise, you'
re
asking for basically non-maintainable code.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||And if you use SQL Server 2005, you can use nested FOR XML PATH expressions
to make the code more modular and maintainable...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74ed968c7ba38196ca340@.news.microsoft.com...
> Hello Toby,
> If you only have those three possibilities to worry about, consider using
> a T-SQL case based on the parameter value, then using having a clause that
> executes the correct FOR XML EXPLICIT query as part of that. Otherwise,
> you're asking for basically non-maintainable code.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

dynamic where prob

hi!

i can't seem to get this right...

create procedure PRstudentInfo

(

@.pStudentCode varchar(20) = NULL,

@.pHidden bit

)

as

begin

declare @.select varchar(max), @.where varchar(max)

set @.select =

'select b.name, c.studentcode from basicinfo b inner join fullinfo c on b.code = c.studentcode'

set @.where =

'WHERE isnull(c.studentcode,'''') ' + 'like ' + '''%' + isnull(@.pStudentCode,'') + '%''' + ' and ' +

' isnull(c.hidden,'''') = ''' + isnull(@.pHidden,'') '' --> here is the error

exec(@.select + @.where)

end

--

the error message is :

Incorrect syntax near ''

i also tried this:

' isnull(c.confidential,'''') = ''' + isnull(@.pConfidential,'') + ' '

The data types varchar and bit are incompatible in the add operator.

A tip : add the following code line:

print @.select + @.where , to see how sql string is made.

I suppose you have to put a space :

'hereWHERE isnull(c.studentcode,'''') etc

|||

Change it to this:

' isnull(c.hidden,'''') = ''' + cast(isnull(@.pHidden,'') as char(1))

|||WOuld be nice if you can describe the next time, what you are trying to do within your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Dynamic where in stored procedure help

Hi all,

I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample,
where field1= coalesce(@.parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution?

Thanks,

JamesYou're on the right track. You'll find a great article at http://www.sommarskog.se/dyn-search.html.|||it is better then dynamic sql

you can use isnull function also|||I think it depends.

My understanding is that with dynamic WHERE clauses, SQL Server does not necessarily always store the most efficient execution plan; in the long run, this can hurt the performance of your query, especially if you have a ton of such dymanic elements in the where clause.

That said, I hate maintaining dynamic sql code.

ou might run some tests and see what you get performance-wise.|||But, on the OTHER hand, rebuilding an execution plan each time may be better for the particular combination of parameters than using the one excution plan that was built the first time for the query. E.g., if there are 10 parameters and the first time through, you enter values for all 10. An execution plan is built. Then, the normal user searches with one parameter most of the time. The original execution plan won't be ideal for that query.

It all depends. I went to a conference where some very good examples of both methods were displayed, profiled, etc. Very enlightening. Test and test some more for your particular case.|||I made some performance tests one day concerning the issue:

http://blogs.x2line.com/al/archive/2004/03/01/189.aspx

Dynamic WHERE Clause to Stored Procedure

Hi all!
I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:

----------------
@.crit varchar(100)

SELECT fldID, fldName FROM tblUsers
WHERE @.crit
----------------

Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.

cheers!
pelleU just pass a parameter @.crit into ur stored procedure and do the following with it inside:

EXEC('SELECT fldID, fldName FROM tblUsers WHERE' + @.crit );

This should help u... I hope.
Alex.|||Another example:

CREATE PROCEDURE [dbo].[Alter_Email_Users_Table]
(@.Column as nvarchar(50),
@.FieldType as nvarchar(50),
@.TableName as nvarchar(50),
@.Null as nvarchar(20),
@.Default as nvarchar(4000)
)
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'ALTER TABLE ' + @.TableName + ' ADD ' + @.Column + ' ' + @.FieldType + ' ' + @.NULL + ' '
IF @.Default IS NOT NULL
SET @.SQL = @.SQL + ' Default ' + "'" + @.Default + "'"
exec (@.SQL)
GO

exec is the key part in both examples.

Dynamic Where clause in Stored Procedure

Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @.Path, @.Status, @.Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.dynamic where clauses would eliminate most of the benefits of the stored procedure.

For every ad-hoc query that's executed, a new execution plan and compiliation takes place. Furthermore, your ram goes up.

A lot of databases will slow down with usage due to this. Therefore, hardcoding your where clauses is best. (like field = @.value)