Immutability of procedure parameters

So, I was having just the most frustrating bug a few months ago, and my
boss says, "Oh, yeah, you should never assign a new value to an IN
parameter in SQL Server."
He couldn't give me a reference -- I'm happy enough not to do it, and
it solved that problem, but I really can't suggest to _other people
that they copy values and so forth unless I can point to some
documentation, which I've been unable to find. Is it anywhere written
that "assigning new values to IN parameters in SQL Server stored
procedures may product unpredictable results?"
Thanks!
IonIon
Do you mean that you get different data back, or the performance is
different.
Obviously, if you have a procedure that finds customers given their id
number, and you call the procedure with customer ID 112 and then the
procedure changes that value to 220 before searching, you'll get results for
different customer than what you were expecting.
I've also found cases of performance problems because of this, because the
proc is optimized based on the value passed IN, and then if you change it to
another value, it still uses the original plan based on the original value.
You should keep parameters as they were, and if you need to modify them,
assign the new value to a local variable, so it is clear what values are
parameters and which are variables. They are treated very differently.
HTH
Kalen Delaney, SQL Server MVP
<ionFreeman@.gmail.com> wrote in message
news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...
> So, I was having just the most frustrating bug a few months ago, and my
> boss says, "Oh, yeah, you should never assign a new value to an IN
> parameter in SQL Server."
> He couldn't give me a reference -- I'm happy enough not to do it, and
> it solved that problem, but I really can't suggest to _other people
> that they copy values and so forth unless I can point to some
> documentation, which I've been unable to find. Is it anywhere written
> that "assigning new values to IN parameters in SQL Server stored
> procedures may product unpredictable results?"
> Thanks!
> Ion
>|||Kalen,
Thanks for responding. The issue that I was seeing was that if I
modified a passed date parameter directly, the stored procedure never
came back -- the connection wasn't closed and no data was returned. The
calling application indicates that it wished to cover a maximal date
range by passing identical start and end times, and if I tried to
implement that logic by modifying the passed dates themselves, I lost
contact with the process -- it was really pretty mysterious.
It's possible this plan business is the ticket -- the proc expected
to return no data (based on its infinitesimal date range), but ended up
with all of it.
Thanks a bunch! Poking around in TechNet, I found
http://www.microsoft.com/technet/pr...comp.mspx#EDUAE
which asserts your point.
Ion
Kalen Delaney wrote:
> Ion
> Do you mean that you get different data back, or the performance is
> different.
> Obviously, if you have a procedure that finds customers given their id
> number, and you call the procedure with customer ID 112 and then the
> procedure changes that value to 220 before searching, you'll get results f
or
> different customer than what you were expecting.
> I've also found cases of performance problems because of this, because the
> proc is optimized based on the value passed IN, and then if you change it
to
> another value, it still uses the original plan based on the original value
.
> You should keep parameters as they were, and if you need to modify them,
> assign the new value to a local variable, so it is clear what values are
> parameters and which are variables. They are treated very differently.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <ionFreeman@.gmail.com> wrote in message
> news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...sql

Image data type

I have been asked to write a piece of code that will insert an image object into a database using a stored procedure and the Microsoft Enterprise Library. Has anyone done this before? Do you have any code examples about how to update a database with an image datatype that needs to be chunked, etc...

In this instance, I need to open up a word document and save the contents as an image in a database.

basically you want to get the uploaded file into a byte array and then you can assign it to a sql param of type image, no chunking needed

here's a snip from one of my projects. I'm using business objects so its not showing the actual sql code but behind the scenes it is just assigning it to a param of type image

byte[] fileBytes = new byte[replacementFileInput.ContentLength];
Stream contentStream = replacementFileInput.FileContent;

contentStream.Read(fileBytes, 0, (int) replacementFileInput.ContentLength);
contentStream.Close();

DocumentFile newFile = new DocumentFile(true);
newFile.DocumentID = originalDocument.ID;
newFile.DocumentType = documentType.Name;
newFile.DocumentImage = fileBytes;
newFile.Save();

I should note that in my example I'm usingNeatUpload, so replacementFileInput is the NeatUpload file input and has a little different syntax then the regular .NET file input

NeatUpload can handle large file uploads gracefully with a progress bar and is free and open source

Hope it helps,

Joe|||

I downloaded the NeatUpload. This does look pretty cool. However, in the instance I'm currently in, I have a Word Document already on the server that I need to convert to a binary object and upload to SQL. The answer is probably right in front of me, so... following your initial tip.

How do I convert an existingWord Document into a byte array?

|||Yes, you can easily do this. In my previous example I was using the contentStream from the uploaded file, but any subclass of stream could be used, so in your case FileStream which you can get with something like this:

FileStream fileStream = File.Open("pathtoyourfile", FileMode.Open);
byte[] fileBytes = new byte[fileStream.Length];
fileStream.Read(fileBytes, 0, (int)fileStream.Length);

now fileBytes has the file and you can assign it to your sql image param

Hope it helps,

Joe|||

This is great, thanks! I think I got the record in there... Now I just have to read it!

Thanks for your help, Joe.

-Scott

|||

Okay, I have been struggling to convert the following code from C# to VB. Particularly at the point of creating the New Byte. Can anyone help?

SqlCommand cmdSelect=new SqlCommand("select Picture" +
" from tblImgData whereID=@.ID",this.sqlConnection1);
cmdSelect.Parameters.Add("@.ID",SqlDbType.Int,4);
cmdSelect.Parameters["@.ID"].Value=this.editID.Text;

this.sqlConnection1.Open();
byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();
string strfn=Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs=new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg,0,barrImg.Length);
fs.Flush();
fs.Close();
pictureBox1.Image=Image.FromFile(strfn);

IIS ODBC logging with extended stored procedure on a trigger

Hello,
I passed several days trying to configure SQL Server 2000 with no success,
so may be someone can help me with this problem.
What I want to do is to log data from an IIS using ODBC logging but in a
diferent way, I mean to a different table structure, so my idea is to create
the standard table that IIS needs to log and insert an instead of trigger
that collect and transform some of the columns of the standard table and
insert the processed results to my new table.
Up to here this worked perfectly, I was able to use the instead of trigger
functionality and insert to my table with no problems.
The problem comes when I try to use an extended stored procedure called from
the trigger, this procedure is responsible to do the transformation IP to
country code, it works very nice outside the trigger.
To sumarize, the weird thing is the following :
On the trigger code, if I comment the line that calls to the extended
sotored procedure, all works ok.
But whenever I try to call it, it seems that the trigger aborts the
execution, and nothing is inserted to my table.
Alse IIS raise this error on event logs :
"IIS ODBC Logging failed to log data to data source WEBSTATS. For
additional information specific to this message please visit the Microsoft
Online Support site located at:
http://www.microsoft.com/contentredirect.asp"
All the permissions on tables and the extended stored procedure are the more
relaxed as I can configure...
If someone can give me some advice, I would really apresiate it !
Thanks !
Although I don't have any idea re the actual problem.. it isn't a good thing
to do extended SP calls in a trigger anyway... Any error will abort the
trigger (which might be what is happening), and then you get a scope
abort...
Perhaps you could have another program run every x minutes and update the
table with the info outside of the trigger..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"-ND-" <death@.elrancho.com> wrote in message
news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I passed several days trying to configure SQL Server 2000 with no success,
> so may be someone can help me with this problem.
> What I want to do is to log data from an IIS using ODBC logging but in a
> diferent way, I mean to a different table structure, so my idea is to
create
> the standard table that IIS needs to log and insert an instead of trigger
> that collect and transform some of the columns of the standard table and
> insert the processed results to my new table.
> Up to here this worked perfectly, I was able to use the instead of trigger
> functionality and insert to my table with no problems.
> The problem comes when I try to use an extended stored procedure called
from
> the trigger, this procedure is responsible to do the transformation IP to
> country code, it works very nice outside the trigger.
> To sumarize, the weird thing is the following :
> On the trigger code, if I comment the line that calls to the extended
> sotored procedure, all works ok.
> But whenever I try to call it, it seems that the trigger aborts the
> execution, and nothing is inserted to my table.
> Alse IIS raise this error on event logs :
> "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> additional information specific to this message please visit the Microsoft
> Online Support site located at:
> http://www.microsoft.com/contentredirect.asp"
> All the permissions on tables and the extended stored procedure are the
more
> relaxed as I can configure...
> If someone can give me some advice, I would really apresiate it !
> Thanks !
>
|||Thanks, for response.
The extended stored procedure doesn't seem to be executed.
If I call it from outside the trigger, with the exact same arguments it
works ok.
So, it should be some permission issue ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> Although I don't have any idea re the actual problem.. it isn't a good
thing[vbcol=seagreen]
> to do extended SP calls in a trigger anyway... Any error will abort the
> trigger (which might be what is happening), and then you get a scope
> abort...
> Perhaps you could have another program run every x minutes and update the
> table with the info outside of the trigger..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "-ND-" <death@.elrancho.com> wrote in message
> news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
success,[vbcol=seagreen]
> create
trigger[vbcol=seagreen]
trigger[vbcol=seagreen]
> from
to[vbcol=seagreen]
Microsoft
> more
>
|||After all, it was a bug in the extended stored procedure that was causing a
fatal error, and it was reflected only inside the trigger... nothing to do
with the server...
Thanks BTW !
"-ND-" <death@.elrancho.com> wrote in message
news:uNSm5VaSEHA.3660@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks, for response.
> The extended stored procedure doesn't seem to be executed.
> If I call it from outside the trigger, with the exact same arguments it
> works ok.
> So, it should be some permission issue ?
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> thing
the[vbcol=seagreen]
> success,
a[vbcol=seagreen]
> trigger
and[vbcol=seagreen]
> trigger
called[vbcol=seagreen]
> to
> Microsoft
the
>

IIS ODBC logging with extended stored procedure on a trigger

Hello,
I passed several days trying to configure SQL Server 2000 with no success,
so may be someone can help me with this problem.
What I want to do is to log data from an IIS using ODBC logging but in a
diferent way, I mean to a different table structure, so my idea is to create
the standard table that IIS needs to log and insert an instead of trigger
that collect and transform some of the columns of the standard table and
insert the processed results to my new table.
Up to here this worked perfectly, I was able to use the instead of trigger
functionality and insert to my table with no problems.
The problem comes when I try to use an extended stored procedure called from
the trigger, this procedure is responsible to do the transformation IP to
country code, it works very nice outside the trigger.
To sumarize, the weird thing is the following :
On the trigger code, if I comment the line that calls to the extended
sotored procedure, all works ok.
But whenever I try to call it, it seems that the trigger aborts the
execution, and nothing is inserted to my table.
Alse IIS raise this error on event logs :
"IIS ODBC Logging failed to log data to data source WEBSTATS. For
additional information specific to this message please visit the Microsoft
Online Support site located at:
http://www.microsoft.com/contentredirect.asp"
All the permissions on tables and the extended stored procedure are the more
relaxed as I can configure...
If someone can give me some advice, I would really apresiate it !
Thanks !Although I don't have any idea re the actual problem.. it isn't a good thing
to do extended SP calls in a trigger anyway... Any error will abort the
trigger (which might be what is happening), and then you get a scope
abort...
Perhaps you could have another program run every x minutes and update the
table with the info outside of the trigger..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"-ND-" <death@.elrancho.com> wrote in message
news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I passed several days trying to configure SQL Server 2000 with no success,
> so may be someone can help me with this problem.
> What I want to do is to log data from an IIS using ODBC logging but in a
> diferent way, I mean to a different table structure, so my idea is to
create
> the standard table that IIS needs to log and insert an instead of trigger
> that collect and transform some of the columns of the standard table and
> insert the processed results to my new table.
> Up to here this worked perfectly, I was able to use the instead of trigger
> functionality and insert to my table with no problems.
> The problem comes when I try to use an extended stored procedure called
from
> the trigger, this procedure is responsible to do the transformation IP to
> country code, it works very nice outside the trigger.
> To sumarize, the weird thing is the following :
> On the trigger code, if I comment the line that calls to the extended
> sotored procedure, all works ok.
> But whenever I try to call it, it seems that the trigger aborts the
> execution, and nothing is inserted to my table.
> Alse IIS raise this error on event logs :
> "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> additional information specific to this message please visit the Microsoft
> Online Support site located at:
> http://www.microsoft.com/contentredirect.asp"
> All the permissions on tables and the extended stored procedure are the
more
> relaxed as I can configure...
> If someone can give me some advice, I would really apresiate it !
> Thanks !
>|||Thanks, for response.
The extended stored procedure doesn't seem to be executed.
If I call it from outside the trigger, with the exact same arguments it
works ok.
So, it should be some permission issue ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> Although I don't have any idea re the actual problem.. it isn't a good
thing
> to do extended SP calls in a trigger anyway... Any error will abort the
> trigger (which might be what is happening), and then you get a scope
> abort...
> Perhaps you could have another program run every x minutes and update the
> table with the info outside of the trigger..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "-ND-" <death@.elrancho.com> wrote in message
> news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
success,[vbcol=seagreen]
> create
trigger[vbcol=seagreen]
trigger[vbcol=seagreen]
> from
to[vbcol=seagreen]
Microsoft[vbcol=seagreen]
> more
>|||After all, it was a bug in the extended stored procedure that was causing a
fatal error, and it was reflected only inside the trigger... nothing to do
with the server...
Thanks BTW !
"-ND-" <death@.elrancho.com> wrote in message
news:uNSm5VaSEHA.3660@.tk2msftngp13.phx.gbl...
> Thanks, for response.
> The extended stored procedure doesn't seem to be executed.
> If I call it from outside the trigger, with the exact same arguments it
> works ok.
> So, it should be some permission issue ?
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> thing
the[vbcol=seagreen]
> success,
a[vbcol=seagreen]
> trigger
and[vbcol=seagreen]
> trigger
called[vbcol=seagreen]
> to
> Microsoft
the[vbcol=seagreen]
>

IIS ODBC logging with extended stored procedure on a trigger

Hello,
I passed several days trying to configure SQL Server 2000 with no success,
so may be someone can help me with this problem.
What I want to do is to log data from an IIS using ODBC logging but in a
diferent way, I mean to a different table structure, so my idea is to create
the standard table that IIS needs to log and insert an instead of trigger
that collect and transform some of the columns of the standard table and
insert the processed results to my new table.
Up to here this worked perfectly, I was able to use the instead of trigger
functionality and insert to my table with no problems.
The problem comes when I try to use an extended stored procedure called from
the trigger, this procedure is responsible to do the transformation IP to
country code, it works very nice outside the trigger.
To sumarize, the weird thing is the following :
On the trigger code, if I comment the line that calls to the extended
sotored procedure, all works ok.
But whenever I try to call it, it seems that the trigger aborts the
execution, and nothing is inserted to my table.
Alse IIS raise this error on event logs :
"IIS ODBC Logging failed to log data to data source WEBSTATS. For
additional information specific to this message please visit the Microsoft
Online Support site located at:
http://www.microsoft.com/contentredirect.asp"
All the permissions on tables and the extended stored procedure are the more
relaxed as I can configure...
If someone can give me some advice, I would really apresiate it !
Thanks !Although I don't have any idea re the actual problem.. it isn't a good thing
to do extended SP calls in a trigger anyway... Any error will abort the
trigger (which might be what is happening), and then you get a scope
abort...
Perhaps you could have another program run every x minutes and update the
table with the info outside of the trigger..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"-ND-" <death@.elrancho.com> wrote in message
news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I passed several days trying to configure SQL Server 2000 with no success,
> so may be someone can help me with this problem.
> What I want to do is to log data from an IIS using ODBC logging but in a
> diferent way, I mean to a different table structure, so my idea is to
create
> the standard table that IIS needs to log and insert an instead of trigger
> that collect and transform some of the columns of the standard table and
> insert the processed results to my new table.
> Up to here this worked perfectly, I was able to use the instead of trigger
> functionality and insert to my table with no problems.
> The problem comes when I try to use an extended stored procedure called
from
> the trigger, this procedure is responsible to do the transformation IP to
> country code, it works very nice outside the trigger.
> To sumarize, the weird thing is the following :
> On the trigger code, if I comment the line that calls to the extended
> sotored procedure, all works ok.
> But whenever I try to call it, it seems that the trigger aborts the
> execution, and nothing is inserted to my table.
> Alse IIS raise this error on event logs :
> "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> additional information specific to this message please visit the Microsoft
> Online Support site located at:
> http://www.microsoft.com/contentredirect.asp"
> All the permissions on tables and the extended stored procedure are the
more
> relaxed as I can configure...
> If someone can give me some advice, I would really apresiate it !
> Thanks !
>|||Thanks, for response.
The extended stored procedure doesn't seem to be executed.
If I call it from outside the trigger, with the exact same arguments it
works ok.
So, it should be some permission issue ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> Although I don't have any idea re the actual problem.. it isn't a good
thing
> to do extended SP calls in a trigger anyway... Any error will abort the
> trigger (which might be what is happening), and then you get a scope
> abort...
> Perhaps you could have another program run every x minutes and update the
> table with the info outside of the trigger..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "-ND-" <death@.elrancho.com> wrote in message
> news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> > I passed several days trying to configure SQL Server 2000 with no
success,
> > so may be someone can help me with this problem.
> >
> > What I want to do is to log data from an IIS using ODBC logging but in a
> > diferent way, I mean to a different table structure, so my idea is to
> create
> > the standard table that IIS needs to log and insert an instead of
trigger
> > that collect and transform some of the columns of the standard table and
> > insert the processed results to my new table.
> >
> > Up to here this worked perfectly, I was able to use the instead of
trigger
> > functionality and insert to my table with no problems.
> > The problem comes when I try to use an extended stored procedure called
> from
> > the trigger, this procedure is responsible to do the transformation IP
to
> > country code, it works very nice outside the trigger.
> >
> > To sumarize, the weird thing is the following :
> > On the trigger code, if I comment the line that calls to the extended
> > sotored procedure, all works ok.
> > But whenever I try to call it, it seems that the trigger aborts the
> > execution, and nothing is inserted to my table.
> > Alse IIS raise this error on event logs :
> > "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> > additional information specific to this message please visit the
Microsoft
> > Online Support site located at:
> > http://www.microsoft.com/contentredirect.asp"
> >
> > All the permissions on tables and the extended stored procedure are the
> more
> > relaxed as I can configure...
> >
> > If someone can give me some advice, I would really apresiate it !
> > Thanks !
> >
> >
>|||After all, it was a bug in the extended stored procedure that was causing a
fatal error, and it was reflected only inside the trigger... nothing to do
with the server...
Thanks BTW !
"-ND-" <death@.elrancho.com> wrote in message
news:uNSm5VaSEHA.3660@.tk2msftngp13.phx.gbl...
> Thanks, for response.
> The extended stored procedure doesn't seem to be executed.
> If I call it from outside the trigger, with the exact same arguments it
> works ok.
> So, it should be some permission issue ?
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> > Although I don't have any idea re the actual problem.. it isn't a good
> thing
> > to do extended SP calls in a trigger anyway... Any error will abort the
> > trigger (which might be what is happening), and then you get a scope
> > abort...
> >
> > Perhaps you could have another program run every x minutes and update
the
> > table with the info outside of the trigger..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "-ND-" <death@.elrancho.com> wrote in message
> > news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> > > Hello,
> > > I passed several days trying to configure SQL Server 2000 with no
> success,
> > > so may be someone can help me with this problem.
> > >
> > > What I want to do is to log data from an IIS using ODBC logging but in
a
> > > diferent way, I mean to a different table structure, so my idea is to
> > create
> > > the standard table that IIS needs to log and insert an instead of
> trigger
> > > that collect and transform some of the columns of the standard table
and
> > > insert the processed results to my new table.
> > >
> > > Up to here this worked perfectly, I was able to use the instead of
> trigger
> > > functionality and insert to my table with no problems.
> > > The problem comes when I try to use an extended stored procedure
called
> > from
> > > the trigger, this procedure is responsible to do the transformation IP
> to
> > > country code, it works very nice outside the trigger.
> > >
> > > To sumarize, the weird thing is the following :
> > > On the trigger code, if I comment the line that calls to the extended
> > > sotored procedure, all works ok.
> > > But whenever I try to call it, it seems that the trigger aborts the
> > > execution, and nothing is inserted to my table.
> > > Alse IIS raise this error on event logs :
> > > "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> > > additional information specific to this message please visit the
> Microsoft
> > > Online Support site located at:
> > > http://www.microsoft.com/contentredirect.asp"
> > >
> > > All the permissions on tables and the extended stored procedure are
the
> > more
> > > relaxed as I can configure...
> > >
> > > If someone can give me some advice, I would really apresiate it !
> > > Thanks !
> > >
> > >
> >
> >
>

iif/switch statement does not work in input parameter expression

Hi There,

One of the parameters that i need to pass to a stored procedure of my
report, i wish to dynamically calculate from another input default parameter

lets say parameter 'DrillDown' = "ABCDEFG"

paramenter 'MyLevel' is calculated by either of the following a switch or
iif statement:

=switch
(left(Parameters!DrillDown.Value,1)="A",1,
left(Parameters!DrillDown.Value,1)="B",2,
left(Parameters!DrillDown.Value,1)="C",3,
left(Parameters!DrillDown.Value,1)="D",4,
left(Parameters!DrillDown.Value,1)="E",5,
left(Parameters!DrillDown.Value,1)="F",6,
left(Parameters!DrillDown.Value,1)="G",7)
--or--
=iif(left(Parameters!RepLvl.Value,1) = "A",1,
iif(left(Parameters!RepLvl.Value,1) = "B",2,5))

Trouble is none of the 2 above work!

i get the following error: The value expression for the report parameter
‘MyLevel’ contains an error: [BC30201] Expression expected.

Wierd thing is if i just have '=iif(left(Parameters!RepLvl.Value,1) =
"A",1,5)' without an 'else if' it works fine !

Can any one help please!

Cheers

Dave

why not use custom code?

I have found for complicated IIF statements, it's easier and safer to create a custom code function.

|||

I would use a code function but for input parameters on start of a report you cant assign them to a 'code.' parameter. Can you? I get an error when i try this.

I got it to work , by taking out all the spaces ?!?!?

Wierdly it works with the spaces in if i have the expression elsewhere in the report, for intsance in a text box .

Anyhow thanks for the reply

Cheers

Dave

iif stored procedure

Hi all,

I have a bunch of queries initially written for MS Access that contain iif
function.
Since I'm new in SQL Server and I don't like the idea of rewriting all
queries using CASE statement, I hope someone has written iif T-SQL stored
procedure.
If someone have T-SQL code of such procedure I would appreciate you post
this code here.

Thanks in advance!"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.ls219.htnet.hr...
> Hi all,
> I have a bunch of queries initially written for MS Access that contain iif
> function.
> Since I'm new in SQL Server and I don't like the idea of rewriting all
> queries using CASE statement, I hope someone has written iif T-SQL stored
> procedure.
> If someone have T-SQL code of such procedure I would appreciate you post
> this code here.
> Thanks in advance!

Since IIF doesn't exist in TSQL, I don't think you have any other option.
The Upsizing Wizard might help, but the most reliable thing to do would be
to rewrite them.

Simon|||"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.ls219.htnet.hr...
> Hi all,
> I have a bunch of queries initially written for MS Access that contain iif
> function.
> Since I'm new in SQL Server and I don't like the idea of rewriting all
> queries using CASE statement, I hope someone has written iif T-SQL stored
> procedure.
> If someone have T-SQL code of such procedure I would appreciate you post
> this code here.
> Thanks in advance!

I think maybe you could write a udf which took parameters.
I think handling the various comparisons might be hard work.
Off the top of my head, I think you'd have to change the syntax of your
query a bit so you handed the comparison as a parameter.
And... erm... changing your query is what you wanted to avoid.

I've got some other bad news for you as well.

MS Access sql is slightly different from sql server sql .
I would suggest you need to go through all your queries and check em out.
Changing iif to case statements is likely just part of the task you face.

--
Regards,
Andy O'Neill|||Andy O'Neill (aon14nocannedmeat@.lycos.co.uk) writes:
> I think maybe you could write a udf which took parameters.
> I think handling the various comparisons might be hard work.
> Off the top of my head, I think you'd have to change the syntax of your
> query a bit so you handed the comparison as a parameter.
> And... erm... changing your query is what you wanted to avoid.

Since boolean is not a datatype in SQL Server, you cannot write a
UDF which permits you to say:

SECECT dho.iif(@.x = @.y, 1, 2)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95F7EFAAF416CYazorman@.127.0.0.1...
> Andy O'Neill (aon14nocannedmeat@.lycos.co.uk) writes:
>> I think maybe you could write a udf which took parameters.
>> I think handling the various comparisons might be hard work.
>> Off the top of my head, I think you'd have to change the syntax of your
>> query a bit so you handed the comparison as a parameter.
>> And... erm... changing your query is what you wanted to avoid.
> Since boolean is not a datatype in SQL Server, you cannot write a
> UDF which permits you to say:
> SECECT dho.iif(@.x = @.y, 1, 2)

I had in mind handing over the comparison operation as a string.
So check if the comparison to be made is varchar and '=' or maybe '>' or
'<'.
If that makes sense.

That's the part I'm actually most confident I could handle.
I didn't even begin to think about the various datatypes one could have for
other parameters...

--
Regards,
Andy O'Neill