passing or defaulting null

I wanted to know what are the advantages/divantages of passing null value
s
in the sp as oppose to setting them as default in the tables.
I am using SQL 2005, "set ANSI_NULLS ON".
ThanksMumbai_Chef wrote:
> I wanted to know what are the advantages/divantages of passing
> null values in the sp as oppose to setting them as default in the
> tables.
> I am using SQL 2005, "set ANSI_NULLS ON".
>
If you have, for example, a DateEntered column, which will always need to be
initialized to the current date and time when a row is inserted and never
subsequently updated, then by all means create a default constraint for the
column. This allows you to never even have to mention the column in any
UPDATE/INSERT DML queries, and as a result, you never have to declare a
parameter for this columns value in any of your procedures.
On the other hand, if you have a column for which you will sometimes be
providing a non-default value, then obviously you will need to include a
parmaeter for that value in your stored procedures. Whether or not you
declare the parameter with a default value (making it an optional parameter)
is totally up to you.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Passing a database name as a parameter

Does SQL Server allow you to pass in a Database name and use it in a stored
procedure? I need to do a join on two tables in two different databases.
Here is some code example that I'm trying to accomplish
//call in C# to add a parameter to the command object
cmd.AddInParameter("@.dbname", DbType.String, "dbName.dbo");
--Stored Procedure--
DECLARE @.dbname varchar(50) (this will be "dbname.dbo")
SELECT * From
table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
Sooooooooo....this is quite a scaled down version of the stored procedure
I'm working on, but the premise is that I have to join tables in two
different databases and I want to be able to do this without having to hard
code "dbname.dbo." for every plase in the SP that is accessing tables from
this second database because eventually the SP will be broken when the
database is moved from our development environment. All references to that
one database would have to be different for each environment we have.
Thanks in advance,
John Scott."John Scott" <johnscott@.despammed.com> wrote in message
news:327E4E45-53BE-44C9-815C-865E2E4E384C@.microsoft.com...
> Does SQL Server allow you to pass in a Database name and use it in a
> stored
> procedure? I need to do a join on two tables in two different databases.
>
> Here is some code example that I'm trying to accomplish
> //call in C# to add a parameter to the command object
> cmd.AddInParameter("@.dbname", DbType.String, "dbName.dbo");
>
> --Stored Procedure--
> DECLARE @.dbname varchar(50) (this will be "dbname.dbo")
> SELECT * From
> table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
>
>
> Sooooooooo....this is quite a scaled down version of the stored procedure
> I'm working on, but the premise is that I have to join tables in two
> different databases and I want to be able to do this without having to
> hard
> code "dbname.dbo." for every plase in the SP that is accessing tables from
> this second database because eventually the SP will be broken when the
> database is moved from our development environment. All references to
> that
> one database would have to be different for each environment we have.
> --
> Thanks in advance,
> John Scott.
I would either create a view to reference the table in the other database or
I would create a synonym (in 2005 only). That way the database name is only
coded in one place per object and it's easy to parameterize that db name at
install time. Much easier than trying to do it dynamically in a proc anyway.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the very quick reply David!!
Unfortuanately we are using SQL 2000 and as far as creating a view to
represent a certain table from the second database the database reference in
each view would have to be changed to point at the proper database when
switching environments. I basically need a configureable way to change the
reference to this second database..
if i can't pass in a parameter value....I could live with something like
this:
----
--
DECLARE @.dbname varchar(50)
set @.dbname = "dbname.dbo"
SELECT * From
table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
----
--
Can I do that? Or is there a way to represent a variable as a database
object somehow'
Thanks again,
John Scott.
"David Portas" wrote:

> "John Scott" <johnscott@.despammed.com> wrote in message
> news:327E4E45-53BE-44C9-815C-865E2E4E384C@.microsoft.com...
> I would either create a view to reference the table in the other database
or
> I would create a synonym (in 2005 only). That way the database name is onl
y
> coded in one place per object and it's easy to parameterize that db name a
t
> install time. Much easier than trying to do it dynamically in a proc anywa
y.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||John Scott wrote:
> each view would have to be changed to point at the proper database when
> switching environments.
Exactly. Do that in the installation script when you create the views.
If you mean you must switch environments at runtime then I'd say that
was best achieved through connection strings in client code, but that
depends what you are doing of course.

> if i can't pass in a parameter value....I could live with something like
> this:
> ----
--
> DECLARE @.dbname varchar(50)
> set @.dbname = "dbname.dbo"
> SELECT * From
> table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
> ----
--
>
> Can I do that? Or is there a way to represent a variable as a database
> object somehow'
>
Dynamic SQL. But as general solution that's a nightmare way to kill a
database. Make sure you read:
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Pass text variable to stored proc

Hi,
I use a stored proc to load XML data into tables. The stored proc takes as
input the XML as a text parameter:
spLoadXML (@.XMLText text)
I have a table "tblXMLContent" with a column of text type containing the XML
text.
How do I pass the content of the column to the stored proc?
ThanksIt depends on how you are calling the stored procedure.
If you are calling it from the application (odbc,jdbc) then you should be
able to find the correlated datatype for a text column and pass it in.
If you are calling it from a stored procedure, you can not declare a
variable of type text and therefore I don't think you can unless the xml is
a parameter to that stored procedure as well. The only hack I have been able
to come up with is declare multiple varchar(8000) columns and parse the text
column 8000 bytes at a time into these variables
i.e.
Declare @.vc1 varchar(8000)
,@.vc2 varchar(8000)
Select @.vc1 = substring(text,1,8000)
,@.vc2 = substring(text,8001,8000)
from txtTable
Then I would use the exec statement that would call the proc
i.e.
exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
Hope this helps.
"fleo" <fleo@.discussions.microsoft.com> wrote in message
news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
> Hi,
> I use a stored proc to load XML data into tables. The stored proc takes
> as
> input the XML as a text parameter:
> spLoadXML (@.XMLText text)
> I have a table "tblXMLContent" with a column of text type containing the
> XML
> text.
> How do I pass the content of the column to the stored proc?
> Thanks|||Thanks JI
But OMG!!!! What a waste of time. I can't believe Microsoft haven't
thought of that. I mean they didn't test their OPENXML with normal XML files
'
Do you know if the process is easier in SQL Server 2005?
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>|||I use a DTS to get the text column value into a global variable and call the
sp.
Then I call the DTS from another sp.
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>

Pass a "Begin...End" Block from ASP

Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
I
have a situation where one of my tables contains the column names that I nee
d
to select from another table. I have always used two separate select
statements (with two separate trips to the db) to get the values I need, but
I recently found that I can accomplish the same thing by passing a
Begin...End block like this...
begin
declare @.col_list varchar(8000)
select @.col_list = coalesce(@.col_list + ', ', '') +
approverlabel from approvers
where formid=6 order by approverorder
exec('select ' + @.col_list + ' from formconfigs where pid=2701')
end
Is there a reason why this should not be done? I realize this would be
better if it was implemented in a stored procedure.Yes this would be best in a stored procedure so it can reuse the query plan.
But in any case you don't need a BEGIN - END. If you send it as one batch
it will work fine.
Andrew J. Kelly SQL MVP
"creed1" <creed1@.discussions.microsoft.com> wrote in message
news:75660A09-951F-4D34-88BE-BFC26E13FED7@.microsoft.com...
> Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
> I
> have a situation where one of my tables contains the column names that I
> need
> to select from another table. I have always used two separate select
> statements (with two separate trips to the db) to get the values I need,
> but
> I recently found that I can accomplish the same thing by passing a
> Begin...End block like this...
> begin
> declare @.col_list varchar(8000)
> select @.col_list = coalesce(@.col_list + ', ', '') +
> approverlabel from approvers
> where formid=6 order by approverorder
> exec('select ' + @.col_list + ' from formconfigs where pid=2701')
> end
> Is there a reason why this should not be done? I realize this would be
> better if it was implemented in a stored procedure.

Partitioning using Date - TimeKey (Ref to another table) column

Hello.
I'd like to implement table partitioning for one of my tables in my DB
(Sales History). I'd like to setup partitioning using a date. I'm planning
to use a partition per year that are going to be spread among multiples
Filegroups. This table contains an integer field that link to my TimeKey
table to get the Date of the transaction. I would know how to setup
partitioning if my date was directly in my table but since I have to join to
another table, how can I achieve this? I can't use a range from my integer
since they're not really sorted sequentially. Any help would be
appreciated. Thanks!"Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I'd like to implement table partitioning for one of my tables in my DB
> (Sales History). I'd like to setup partitioning using a date. I'm
> planning to use a partition per year that are going to be spread among
> multiples Filegroups. This table contains an integer field that link to
> my TimeKey table to get the Date of the transaction. I would know how to
> setup partitioning if my date was directly in my table but since I have to
> join to another table, how can I achieve this? I can't use a range from
> my integer since they're not really sorted sequentially. Any help would
> be appreciated. Thanks!
>
Not what you want to hear but I'd be inclined to allocate an intelligent
time key to start with. If it's keyed on date only then use the date as an
8-digit number in the form yyyymmdd.
Possibly you could create a computed column that derives a date from your
key and use that as your partitioning column.
David Portas
SQL Server MVP
--|||I DON'T WANT TO HEAR THAT!
:)
That's what I thought, it is not a big deal since I'm @. the beginning of
implementing our new DW using SQL Server 2005 so I will definitively
consider your "intelligent" key suggestion. Thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news: OQMwB1TEGHA.376@.TK2MSFTNGP12.phx.gbl...
> "Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
> news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Not what you want to hear but I'd be inclined to allocate an intelligent
> time key to start with. If it's keyed on date only then use the date as an
> 8-digit number in the form yyyymmdd.
> Possibly you could create a computed column that derives a date from your
> key and use that as your partitioning column.
> --
> David Portas
> SQL Server MVP
> --
>

partitioning the table

I have some tables in the large production database which suppose to grow hi
gh and obviously the time consumption on making query on those tables will b
e really high, so is there any way by which I can partition the table, I mea
n the records entered befor
e a particular date will go into some separate partition and will be archive
d on requirement the query can be passed to that partition.
Hope this could be the way to handle the large tables. Or if you can suggest
some ways or links to handle very large database then I'll be thankful for
you.
Thanks in advance
Regards,
SunilHi Sunil.
You can use partitioned views to do this. These can either be local or
distributed accross physical servers.
Do these tables have identities? If so, this is a gotcha but I have a work
around if you do.
Regards,
Greg Linwood
SQL Server MVP
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4BC00EDE-6F44-4B1C-ACB0-F77D2035DE53@.microsoft.com...

quote:

---

> I have some tables in the large production database which suppose to grow

---

high and obviously the time consumption on making query on those tables will
be really high, so is there any way by which I can partition the table, I
mean the records entered before a particular date will go into some separate
partition and will be archived on requirement the query can be passed to
that partition.

quote:

---

>
> Hope this could be the way to handle the large tables. Or if you can

---

suggest some ways or links to handle very large database then I'll be
thankful for you.

quote:

---

>
> Thanks in advance
> Regards,
> Sunil

---

|||Design the table again for the larger table, I think the problem is caused
by the design of table|||Partition the tables and use partitioned views to consolidate... You should
do lots of load testing to make sure this scales to suit your needs...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4BC00EDE-6F44-4B1C-ACB0-F77D2035DE53@.microsoft.com...

quote:

---

> I have some tables in the large production database which suppose to grow

---

high and obviously the time consumption on making query on those tables will
be really high, so is there any way by which I can partition the table, I
mean the records entered before a particular date will go into some separate
partition and will be archived on requirement the query can be passed to
that partition.

quote:

---

>
> Hope this could be the way to handle the large tables. Or if you can

---

suggest some ways or links to handle very large database then I'll be
thankful for you.

quote:

---

>
> Thanks in advance
> Regards,
> Sunil

---