Showing posts with label dynamic. Show all posts
Showing posts with label dynamic. Show all posts

Sunday, March 25, 2012

Config SQL Server properties to use Fixed Memory

Can I use an SQL statement to configure an SQL server to use Fixed
Memory size as opposed to the default Dynamic ? I don't want to use
the GUI.
Thanks
Sid.Hello,
You will have to use SP_CONFIGURE system storedprocedure.
Below example will tell SQL Server to use 4 GB of RAM.
SP_CONFIGURE 'max server memory', 4096
RECONFIGURE
GO
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172763475.361495.109810@.p10g2000cwp.googlegroups.com...
> Can I use an SQL statement to configure an SQL server to use Fixed
> Memory size as opposed to the default Dynamic ? I don't want to use
> the GUI.
> Thanks
> Sid.
>|||I suppose you could use
sp_configure with the following arguments:
max server memory (MB)
min server memory (MB)
and then reconfigure. Any particular reason you want to turn off the dynamic
memory management?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I suppose you could use
> sp_configure with the following arguments:
> max server memory (MB)
> min server memory (MB)
> and then reconfigure. Any particular reason you want to turn off the dynam
ic
> memory management?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
We are configuring the memory of a server that runs one of our vendors
applications. If we don't set the memory constraints, the application
will eventually use all the memory and seize the server. Right now we
set the memory as fixed and set its size at half the system memory.
Is there any way to specify the fixed memory with a variable ?
( 50% )
Thanks
Sid.|||No, You will have set the memory in MB based on the physical memory
availability.
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172766417.255720.121360@.30g2000cwc.googlegroups.com...
> On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> We are configuring the memory of a server that runs one of our vendors
> applications. If we don't set the memory constraints, the application
> will eventually use all the memory and seize the server. Right now we
> set the memory as fixed and set its size at half the system memory.
> Is there any way to specify the fixed memory with a variable ?
> ( 50% )
> Thanks
> Sid.
>
>
>|||Something like this should do it:
declare @.x int
select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
from sys.dm_os_sys_info
exec sp_configure 'max server memory (MB)', @.x
reconfigure
exec sp_configure 'min server memory (MB)', @.x
reconfigure
Note that I'd thoroughly test this sort of code first - I haven't thoroughly
tested sys.dm_os_sys_info on different environments yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Something like this should do it:
> declare @.x int
> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
> from sys.dm_os_sys_info
> exec sp_configure 'max server memory (MB)', @.x
> reconfigure
> exec sp_configure 'min server memory (MB)', @.x
> reconfigure
> Note that I'd thoroughly test this sort of code first - I haven't thorough
ly
> tested sys.dm_os_sys_info on different environments yet.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks for the example.
Question: Why are you not setting the 'fixed' as opposed to the Max
and Min ?|||> Question: Why are you not setting the 'fixed' as opposed to the Max
> and Min ?
There is no such setting. There's a max and a min. The GUI fools us into bel
ieving that there's a
fixed, but it only sets max and min to the same value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sid" <sidwelle@.alexian.net> wrote in message
news:1172772610.650684.3930@.v33g2000cwv.googlegroups.com...
> On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Thanks for the example.
> Question: Why are you not setting the 'fixed' as opposed to the Max
> and Min ?
>|||On Mar 1, 12:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There is no such setting. There's a max and a min. The GUI fools us into b
elieving that there's a
> fixed, but it only sets max and min to the same value.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "sid" <sidwe...@.alexian.net> wrote in message
> news:1172772610.650684.3930@.v33g2000cwv.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
When I try to execute I get "Incorrect syntax near the keywork 'as' "
Thanks
Sid.|||Sorry - it's from an earlier query. Please remove "as
Half_physical_memory_in_MB" and run it.
Paul Ibison

Config SQL Server properties to use Fixed Memory

Can I use an SQL statement to configure an SQL server to use Fixed
Memory size as opposed to the default Dynamic ? I don't want to use
the GUI.
Thanks
Sid.
Hello,
You will have to use SP_CONFIGURE system storedprocedure.
Below example will tell SQL Server to use 4 GB of RAM.
SP_CONFIGURE 'max server memory', 4096
RECONFIGURE
GO
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172763475.361495.109810@.p10g2000cwp.googlegr oups.com...
> Can I use an SQL statement to configure an SQL server to use Fixed
> Memory size as opposed to the default Dynamic ? I don't want to use
> the GUI.
> Thanks
> Sid.
>
|||I suppose you could use
sp_configure with the following arguments:
max server memory (MB)
min server memory (MB)
and then reconfigure. Any particular reason you want to turn off the dynamic
memory management?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I suppose you could use
> sp_configure with the following arguments:
> max server memory (MB)
> min server memory (MB)
> and then reconfigure. Any particular reason you want to turn off the dynamic
> memory management?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
We are configuring the memory of a server that runs one of our vendors
applications. If we don't set the memory constraints, the application
will eventually use all the memory and seize the server. Right now we
set the memory as fixed and set its size at half the system memory.
Is there any way to specify the fixed memory with a variable ?
( 50% )
Thanks
Sid.
|||No, You will have set the memory in MB based on the physical memory
availability.
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172766417.255720.121360@.30g2000cwc.googlegro ups.com...
> On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> We are configuring the memory of a server that runs one of our vendors
> applications. If we don't set the memory constraints, the application
> will eventually use all the memory and seize the server. Right now we
> set the memory as fixed and set its size at half the system memory.
> Is there any way to specify the fixed memory with a variable ?
> ( 50% )
> Thanks
> Sid.
>
>
>
|||Something like this should do it:
declare @.x int
select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
from sys.dm_os_sys_info
exec sp_configure 'max server memory (MB)', @.x
reconfigure
exec sp_configure 'min server memory (MB)', @.x
reconfigure
Note that I'd thoroughly test this sort of code first - I haven't thoroughly
tested sys.dm_os_sys_info on different environments yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Something like this should do it:
> declare @.x int
> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
> from sys.dm_os_sys_info
> exec sp_configure 'max server memory (MB)', @.x
> reconfigure
> exec sp_configure 'min server memory (MB)', @.x
> reconfigure
> Note that I'd thoroughly test this sort of code first - I haven't thoroughly
> tested sys.dm_os_sys_info on different environments yet.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks for the example.
Question: Why are you not setting the 'fixed' as opposed to the Max
and Min ?
|||On Mar 1, 12:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There is no such setting. There's a max and a min. The GUI fools us into believing that there's a
> fixed, but it only sets max and min to the same value.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "sid" <sidwe...@.alexian.net> wrote in message
> news:1172772610.650684.3930@.v33g2000cwv.googlegrou ps.com...
>
>
>
>
>
>
> - Show quoted text -
When I try to execute I get "Incorrect syntax near the keywork 'as' "
Thanks
Sid.
|||Sorry - it's from an earlier query. Please remove "as
Half_physical_memory_in_MB" and run it.
Paul Ibison
|||On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Sorry - it's from an earlier query. Please remove "as
> Half_physical_memory_in_MB" and run it.
> Paul Ibison
I had already done that, but I still get " Invalid object name
'sys.dm_os_sys_info' "
I am using SQL 2k
Thanks
Sid.

Config SQL Server properties to use Fixed Memory

Can I use an SQL statement to configure an SQL server to use Fixed
Memory size as opposed to the default Dynamic ? I don't want to use
the GUI.
Thanks
Sid.Hello,
You will have to use SP_CONFIGURE system storedprocedure.
Below example will tell SQL Server to use 4 GB of RAM.
SP_CONFIGURE 'max server memory', 4096
RECONFIGURE
GO
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172763475.361495.109810@.p10g2000cwp.googlegroups.com...
> Can I use an SQL statement to configure an SQL server to use Fixed
> Memory size as opposed to the default Dynamic ? I don't want to use
> the GUI.
> Thanks
> Sid.
>|||I suppose you could use
sp_configure with the following arguments:
max server memory (MB)
min server memory (MB)
and then reconfigure. Any particular reason you want to turn off the dynamic
memory management?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I suppose you could use
> sp_configure with the following arguments:
> max server memory (MB)
> min server memory (MB)
> and then reconfigure. Any particular reason you want to turn off the dynamic
> memory management?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
We are configuring the memory of a server that runs one of our vendors
applications. If we don't set the memory constraints, the application
will eventually use all the memory and seize the server. Right now we
set the memory as fixed and set its size at half the system memory.
Is there any way to specify the fixed memory with a variable ?
( 50% )
Thanks
Sid.|||No, You will have set the memory in MB based on the physical memory
availability.
Thanks
Hari
"sid" <sidwelle@.alexian.net> wrote in message
news:1172766417.255720.121360@.30g2000cwc.googlegroups.com...
> On Mar 1, 10:13 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> I suppose you could use
>> sp_configure with the following arguments:
>> max server memory (MB)
>> min server memory (MB)
>> and then reconfigure. Any particular reason you want to turn off the
>> dynamic
>> memory management?
>> Cheers,
>> Paul Ibison SQL Server MVP,www.replicationanswers.com
> We are configuring the memory of a server that runs one of our vendors
> applications. If we don't set the memory constraints, the application
> will eventually use all the memory and seize the server. Right now we
> set the memory as fixed and set its size at half the system memory.
> Is there any way to specify the fixed memory with a variable ?
> ( 50% )
> Thanks
> Sid.
>
>
>|||Something like this should do it:
declare @.x int
select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
from sys.dm_os_sys_info
exec sp_configure 'max server memory (MB)', @.x
reconfigure
exec sp_configure 'min server memory (MB)', @.x
reconfigure
Note that I'd thoroughly test this sort of code first - I haven't thoroughly
tested sys.dm_os_sys_info on different environments yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Something like this should do it:
> declare @.x int
> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
> from sys.dm_os_sys_info
> exec sp_configure 'max server memory (MB)', @.x
> reconfigure
> exec sp_configure 'min server memory (MB)', @.x
> reconfigure
> Note that I'd thoroughly test this sort of code first - I haven't thoroughly
> tested sys.dm_os_sys_info on different environments yet.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks for the example.
Question: Why are you not setting the 'fixed' as opposed to the Max
and Min ?|||> Question: Why are you not setting the 'fixed' as opposed to the Max
> and Min ?
There is no such setting. There's a max and a min. The GUI fools us into believing that there's a
fixed, but it only sets max and min to the same value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sid" <sidwelle@.alexian.net> wrote in message
news:1172772610.650684.3930@.v33g2000cwv.googlegroups.com...
> On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> Something like this should do it:
>> declare @.x int
>> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
>> from sys.dm_os_sys_info
>> exec sp_configure 'max server memory (MB)', @.x
>> reconfigure
>> exec sp_configure 'min server memory (MB)', @.x
>> reconfigure
>> Note that I'd thoroughly test this sort of code first - I haven't thoroughly
>> tested sys.dm_os_sys_info on different environments yet.
>> Cheers,
>> Paul Ibison SQL Server MVP,www.replicationanswers.com
> Thanks for the example.
> Question: Why are you not setting the 'fixed' as opposed to the Max
> and Min ?
>|||On Mar 1, 12:54 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Question: Why are you not setting the 'fixed' as opposed to the Max
> > and Min ?
> There is no such setting. There's a max and a min. The GUI fools us into believing that there's a
> fixed, but it only sets max and min to the same value.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "sid" <sidwe...@.alexian.net> wrote in message
> news:1172772610.650684.3930@.v33g2000cwv.googlegroups.com...
>
> > On Mar 1, 11:38 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> >> Something like this should do it:
> >> declare @.x int
> >> select @.x = physical_memory_in_bytes / 500.0 as Half_physical_memory_in_MB
> >> from sys.dm_os_sys_info
> >> exec sp_configure 'max server memory (MB)', @.x
> >> reconfigure
> >> exec sp_configure 'min server memory (MB)', @.x
> >> reconfigure
> >> Note that I'd thoroughly test this sort of code first - I haven't thoroughly
> >> tested sys.dm_os_sys_info on different environments yet.
> >> Cheers,
> >> Paul Ibison SQL Server MVP,www.replicationanswers.com
> > Thanks for the example.
> > Question: Why are you not setting the 'fixed' as opposed to the Max
> > and Min ... Hide quoted text -
> - Show quoted text -
When I try to execute I get "Incorrect syntax near the keywork 'as' "
Thanks
Sid.|||Sorry - it's from an earlier query. Please remove "as
Half_physical_memory_in_MB" and run it.
Paul Ibison|||On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Sorry - it's from an earlier query. Please remove "as
> Half_physical_memory_in_MB" and run it.
> Paul Ibison
I had already done that, but I still get " Invalid object name
'sys.dm_os_sys_info' "
I am using SQL 2k
Thanks
Sid.|||For SQL2K, check out below:
sysconfigures
syscurconfigs
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sid" <sidwelle@.alexian.net> wrote in message
news:1172851212.691239.101580@.s48g2000cws.googlegroups.com...
> On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> Sorry - it's from an earlier query. Please remove "as
>> Half_physical_memory_in_MB" and run it.
>> Paul Ibison
> I had already done that, but I still get " Invalid object name
> 'sys.dm_os_sys_info' "
> I am using SQL 2k
> Thanks
> Sid.
>|||On Mar 2, 10:37 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> For SQL2K, check out below:
> sysconfigures
> syscurconfigs
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "sid" <sidwe...@.alexian.net> wrote in message
> news:1172851212.691239.101580@.s48g2000cws.googlegroups.com...
>
> > On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> >> Sorry - it's from an earlier query. Please remove "as
> >> Half_physical_memory_in_MB" and run it.
> >> Paul Ibison
> > I had already done that, but I still get " Invalid object name
> > 'sys.dm_os_sys_info' "
> > I am using SQL 2k
> > Thanks
> > Sid.- Hide quoted text -
> - Show quoted text -
Nice to know those tables are there, but I didn't see a field for
system memory.
Sid.|||You mean row? Did you turn on "show advanced options"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sid" <sidwelle@.alexian.net> wrote in message
news:1172865017.402571.254540@.8g2000cwh.googlegroups.com...
> On Mar 2, 10:37 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> For SQL2K, check out below:
>> sysconfigures
>> syscurconfigs
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "sid" <sidwe...@.alexian.net> wrote in message
>> news:1172851212.691239.101580@.s48g2000cws.googlegroups.com...
>>
>> > On Mar 1, 4:59 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> >> Sorry - it's from an earlier query. Please remove "as
>> >> Half_physical_memory_in_MB" and run it.
>> >> Paul Ibison
>> > I had already done that, but I still get " Invalid object name
>> > 'sys.dm_os_sys_info' "
>> > I am using SQL 2k
>> > Thanks
>> > Sid.- Hide quoted text -
>> - Show quoted text -
> Nice to know those tables are there, but I didn't see a field for
> system memory.
> Sid.
>|||As far as I can tell from digging through the system tables on SQL Server
2000 this info wasn't available to TSQL directly. You could read it in a COM
object and reference that in a stored proc using the sp_oa.. procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Mar 6, 4:32 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> As far as I can tell from digging through the system tables on SQL Server
> 2000 this info wasn't available to TSQL directly. You could read it in a COM
> object and reference that in a stored proc using the sp_oa.. procedures.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks Paul, thats about what I had figured.
I atleast got what I needed out of it.
Sid.

Tuesday, March 20, 2012

conditionally executing sql statement

I need to write .sql file in a dynamic way.
inside the file i need to decide at run time (of the sql script) whether or
not to run a SQL statement that I don't know it's content while dynamically
creating the sql script, this sql statement might include a GO statement
what makes a problem putting it inside an if begin end block.
for example
in my script a runtime check
select @.runStatement = configVal from database at script runtime execution
if @.runStatement =1
begin
-- here comes an unknown sql statement at the time of creating the sql
script that might include a GO command which will break the syntax of the
entire block
-- go <- this here makes a TSQL error for the end command since it
breaks the begin / end block.
end
can someone recomend of an approach for how to solve this?
execute sql is not an option here since the internal SQL statement might be
larget then 4000 nvarchar characters and I can not declarae a @.ntext local
variable
TIA.>> inside the file i need to decide at run time (of the sql script) whether
Under normal circumstances, this is a poor way to write SQL code. The kludgy
workaround is to assign the SQL statement to a variable, replace the tokens
that are not needed and use EXEC or sp_ExecuteSQL to execute it.
The right way can be suggested only if you can explain the overall
situation. Why do you have to resort to such complex approach? Is there a
3rd party tool involved?
Anith|||martin (news.microsoft.com) writes:
> I need to write .sql file in a dynamic way.
> inside the file i need to decide at run time (of the sql script) whether
> or not to run a SQL statement that I don't know it's content while
> dynamically creating the sql script, this sql statement might include a
> GO statement what makes a problem putting it inside an if begin end
> block.
> for example
> in my script a runtime check
> select @.runStatement = configVal from database at script runtime execution
> if @.runStatement =1
> begin
> -- here comes an unknown sql statement at the time of creating the
> sql script that might include a GO command which will break the syntax
> of the entire block
> -- go <- this here makes a TSQL error for the end command since it
> breaks the begin / end block.
> end
>
> can someone recomend of an approach for how to solve this?
> execute sql is not an option here since the internal SQL statement might
> be larget then 4000 nvarchar characters and I can not declarae a @.ntext
> local variable
Are you on SQL 2000 or SQL 2005?
If you are on SQL 2000, I would srtongly recommend that you run the
control loop from a client. It could be very difficult to sort out
from SQL only. It could be a little easier on SQL 2005, since there
you can work with nvarchar(MAX) and you could do the batch splitting
in CLR code.
I echoes Aniths suggestion that you could be better served by telling
us the full story. This could give you better suggestions.
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|||it should support database modifications / upgrades.
the commands are not known at the time of designing the tool that will
execute the statements.
i understand that running each script from a client tool like a VB.NET app
is a good option but is it not possible to run it from sql script file using
some goto label....?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eedlU0VaGHA.1020@.TK2MSFTNGP02.phx.gbl...
> Under normal circumstances, this is a poor way to write SQL code. The
> kludgy workaround is to assign the SQL statement to a variable, replace
> the tokens that are not needed and use EXEC or sp_ExecuteSQL to execute
> it.
>
> The right way can be suggested only if you can explain the overall
> situation. Why do you have to resort to such complex approach? Is there a
> 3rd party tool involved?
> --
> Anith
>|||martin (news.microsoft.com) writes:
> it should support database modifications / upgrades.
> the commands are not known at the time of designing the tool that will
> execute the statements.
> i understand that running each script from a client tool like a VB.NET
> app is a good option but is it not possible to run it from sql script
> file using some goto label....?
Possible and possible. With severe kludges maybe. And it depends on the SQL
Server version.
If the purpose of the tool is run scripts for database changes, I strongly
recommend using a control part in a client language.
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.mspxsqlsql

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.
Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDynvarchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:
> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.sqlsql

Thursday, March 8, 2012

Conditional JOIN

Hi,

I am trying to change an SP from dynamic SQL to proper SQL but I can't
figure a way to conditionally add extra parts to the statement. How
can I do the equivalent of the following?

DECLARE @.arg NVARCHAR(10)

SELECT a.i, a.x
FROM aTable a
IF LEN(@.arg)
BEGIN
INNER JOIN bTable b ON a.[id] = b.[id]
END

Conditionally adding the INNER JOIN is very easy when building up a SQL
string but I can't see how to do it in pure SQL?

Thanks.What do you intend by a conditional join? The purpose of a join is
usually to bring back some extra columns from additional tables. Static
queries also have static metadata (in other words always the same set
of columns are returned every time) so a "conditional" join such as you
have posted is really just a selection:

SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @.arg = ''

(I'm assuming ID is unique in B otherwise your original query might
return duplicate rows).

In general you can use OR to implement optional criteria but this often
leads to sub-optimal query plans. You should consider using IF
statements to choose from a set of possible queries or just break up
the different queries into separate SPs to be called independently.
Either approach is usually preferable to dynamic SQL.

--
David Portas
SQL Server MVP
--|||I possibly gave a less than clear example. Insert an additional join
on aTable:

SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b on a.[id] = b.[id]
IF (LEN(@.arg) > 0)
BEGIN
INNER JOIN cTable c ON a.[id] = c.[id]
END

The condition is that if the parameter @.arg is of length 0 then I do
not want to join to cTable but if it is greater than 0 then I do. It's
essentially a switch to impose additional restrictions upon the
recordset being returned. I am not changing the structure of what is
being selected, just _conditionally_ adding an extra filter on the
data.

Thanks.|||SELECT a.i, a.x
FROM aTable a
INNER JOIN bTable b
ON a.[id] = b.[id]
WHERE EXISTS
(SELECT *
FROM cTable c
WHERE c.[id] = a.[id])
OR @.arg = ''

Again, assuming ID is unique in C this is equivalent to an INNER JOIN.

--
David Portas
SQL Server MVP
--|||(chandy@.totalise.co.uk) writes:
> I possibly gave a less than clear example. Insert an additional join
> on aTable:
> SELECT a.i, a.x
> FROM aTable a
> INNER JOIN bTable b on a.[id] = b.[id]
> IF (LEN(@.arg) > 0)
> BEGIN
> INNER JOIN cTable c ON a.[id] = c.[id]
> END
> The condition is that if the parameter @.arg is of length 0 then I do
> not want to join to cTable but if it is greater than 0 then I do. It's
> essentially a switch to impose additional restrictions upon the
> recordset being returned. I am not changing the structure of what is
> being selected, just _conditionally_ adding an extra filter on the
> data.

And that is exactly what David's query achieved. He hinted that his query
might not get a good query plan, but in fact

SELECT a.i, a.x
FROM aTable AS a
WHERE EXISTS
(SELECT *
FROM bTable AS b
WHERE b.id = a.id)
OR @.arg = ''

should perform very well when @.arg is ''. SQL Server will understand
that it does have to access bTable at all. It will appear in the plan,
but a so-called startup expression prevents it from being accessed when
there is no need to. (Interested readers can find more details on
this in http://www.sommarskog.se/dyn-search...mplexconditions.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ahh, I get it now, hadn't fully comprehended what the WHERE EXISTS was
doing. That seems to work nicely.

Thanks!

Saturday, February 25, 2012

conditional dynamic SQL in stored procedure, not returning any result

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

Hi,

Please check whether the @.condition parameter you have provided can hit in the IF statements. At the end, you don't need to use RETURN if you don't return anything.

I would not suggest you put the condition in your WHERE clause, because it will return an empty result set for the condition that does not meet. And multiple result sets will be returned for all the SELECT statements.

|||

Nitin Pawar:

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

replaceLike by= and then try .. hope it will help

Friday, February 24, 2012

Conditional Column Name On Insert

I need to dynamic select a column in which insert a vale based on a
parameter value, I have this code, but it throws an incorrect syntax
error.

How do I dinamically select a column to insert based on a parameter?

Create PROCEDURE dbo.UpdateDetalleOT (
@.eotId int,
)

insert into OT (
select Case
when @.eotId = 1 THEN OTFechaBorrador
when @.eotId = 2 THEN OTFechaAAsignar
end
) values ...

Best Regards
Fabio Cavassini
http://www.pldsa.com--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Try something like this instead:

If @.eotID = 1
BEGIN
INSERT INTO OT (OTFechaBorrador)
VALUES ...
END

IF @.eotId = 2
BEGIN
INSERT INTO OT (OTFechaAAsignar)
VALUES ...
END
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F9/oechKqOuFEgEQL0DwCfVQyA7xrkbFiBhXHJwcZwh6jlv1sAnj8 x
Ig0V5L9rm9Cpt13pG+Talbie
=0ECz
--END PGP SIGNATURE--

cavassinif@.gmail.com wrote:
> I need to dynamic select a column in which insert a vale based on a
> parameter value, I have this code, but it throws an incorrect syntax
> error.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int,
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ...|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||On 20 Jan 2006 11:49:20 -0800, cavassinif@.gmail.com wrote:

>I need to dynamic select a column in which insert a vale based on a
>parameter value, I have this code, but it throws an incorrect syntax
>error.
>How do I dinamically select a column to insert based on a parameter?
>Create PROCEDURE dbo.UpdateDetalleOT (
>@.eotId int,
>)
>insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
>) values ...
>
>Best Regards
>Fabio Cavassini
>http://www.pldsa.com

Hi Fabio,

You can't insert into just one column - you insert a complete row, and
you'll have to give values (either real values or NULL) for all columns.
Sure, the language permits you to leave out some columns, but that's
just a shorthand way for specifying that you want to insert the defined
DEFAULT value (if any) or NULL in all the other columns.

To do what you appear to want (and I *really* hope that this is an
extremely simplified illustration of the real problem, because if this
is your real procedure, you have much, much bigger problems), you can
either use the code posted by MGFoster, or use

INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar)
SELECT CASE WHEN @.eotId = 1 THEN ... ELSE NULL END),
CASE WHEN @.eotId = 2 THEN ... ELSE NULL END)

--
Hugo Kornelis, SQL Server MVP|||Thanks for all your advices:

MGFoster:
Yes, this would be a solution...but the table in which I'm inserting
has more than 30 columns...the insert code is huge...and I wouldn't
like to copy the insert for just one column of difference.

--CELKO--
Here's the explanation of the case:

Suppose that you have a Job Order that goes over diferrent states
(Draft, Confirmed, Assigned, Finished...)
Well, I need to save the Date when the Job Order changed it's state, so
I have the following columns in the JobOrder Table:
DraftDate : Date when the Job Order get's the Draft state
ConfirmedDate : Date when the Job Order get's the Confirmed state
AssignedDate : Date when the Job Order get's the Assignedstate
etc...

That's why I need to create a dynamic Insert, because depending the
state the Job Order will be saved...will depend which column
(DraftDate, ConfirmedDate, etc) it will have to insert the current
date.

Hugo:
I don't want to select a dynamic value... the value will be always the
current date, I need to dinamically specify in which column I will
insert the current date

Best Regards
Fabio Cavassini|||On 22 Jan 2006 15:12:16 -0800, cavassinif@.gmail.com wrote:

(snip)
>Yes, this would be a solution...but the table in which I'm inserting
>has more than 30 columns...the insert code is huge...and I wouldn't
>like to copy the insert for just one column of difference.

Hi Fabio,

Hmmm. Maybe you could explain in some more detail what is the actual
business problem you're trying to solve. A parameter that governs in
which of 30 columns the current date has to be inserted sounds as if the
best solution would be a redesign of your table - but I can only say for
sure if I know more about your actual problem and your current table
structure.

(snip)
>Hugo:
>I don't want to select a dynamic value... the value will be always the
>current date, I need to dinamically specify in which column I will
>insert the current date

I had used ellipsis as a placeholder for the value to delete. Now that I
know it's the current date, I can complete my proposed code. I've also
added a third column and ellipsis to show how you can extend this to as
many columns as you need.

INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
...

If called with @.eotId equal to 1, this will create a row with
CURRENT_TIMESTAMP in the first column (OTFechaBorrador) and NULL in the
two (or more) other columns. If @.eotId is 2, OTFechaAAsignar will be the
current datetime and the other columns are NULL. Etc, etc.

--
Hugo Kornelis, SQL Server MVP|||Do it in two steps: Insert the common column data first, then do an
update to the appropriate row/column based on the new entry and the
type.

Hugo Kornelis wrote:
> On 22 Jan 2006 15:12:16 -0800, cavassinif@.gmail.com wrote:
> (snip)
> >Yes, this would be a solution...but the table in which I'm inserting
> >has more than 30 columns...the insert code is huge...and I wouldn't
> >like to copy the insert for just one column of difference.
> Hi Fabio,
> Hmmm. Maybe you could explain in some more detail what is the actual
> business problem you're trying to solve. A parameter that governs in
> which of 30 columns the current date has to be inserted sounds as if the
> best solution would be a redesign of your table - but I can only say for
> sure if I know more about your actual problem and your current table
> structure.
> (snip)
> >Hugo:
> >I don't want to select a dynamic value... the value will be always the
> >current date, I need to dinamically specify in which column I will
> >insert the current date
> I had used ellipsis as a placeholder for the value to delete. Now that I
> know it's the current date, I can complete my proposed code. I've also
> added a third column and ellipsis to show how you can extend this to as
> many columns as you need.
> INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
> SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
> ...
> If called with @.eotId equal to 1, this will create a row with
> CURRENT_TIMESTAMP in the first column (OTFechaBorrador) and NULL in the
> two (or more) other columns. If @.eotId is 2, OTFechaAAsignar will be the
> current datetime and the other columns are NULL. Etc, etc.
> --
> Hugo Kornelis, SQL Server MVP|||The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH|||The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH|||Thanks for all the replies,

I implemented it as Hugo sayed, with condition in the value:

>INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
>SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),

I haven't realized that in an insert...in fact all values are
modified...consequently I need to put the condition in the value.

>The problem is your database design. Instead of 30 columns for dates
>A parameter that governs in
>which of 30 columns the current date has to be inserted sounds as if the
>best solution would be a redesign of your table

I know..that there are many columns...but...the business model
requires it. In addition I don't like to have many tables in my
databases, it's too much simple to maintain a reduced (respecting
normal forms, of course) set of tables.

Best Regards
Fabio Cavassini