How to Make a sql procedure for this requirement

Hi,
I am a new comer to sql server. I could write simple procedures. I have
a table like this
Field1 Field2 Field3
1 one name-1
2 two name-2
1 one name-3
I have to write a procedure to return the values of the records for all the
records with Field1 equal to 1 in the format:
1,one,name-1,name-3
As in the above case two records match so I return total four values with
two name values. If three records would have matched I would have returned
three name values in the response, so in that case the total values to be
returned would have been 5.
I just wonder how I would write this procedure some thing like
create procedure dbo.getvalues input int, output1 int OUTPUT, output2
char(10) OUTPUT....
as
select Field1,Field2,Field3 ...
How can I declare a variable limit to output fileld ? I mean the output from
this procedure could be 4 fields 5 fields or 6 fileds or more ....
Thanks for any help in this matter.
JSJS
I'd strongly recommend you doing such reports on the client side. This
solution is not reliable.
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 CHAR(1) NOT NULL,
col3 CHAR(2) NOT NULL
)
INSERT INTO #Test VALUES (1,'A','BB')
INSERT INTO #Test VALUES (2,'G','DD')
INSERT INTO #Test VALUES (1,'A','CC')
DECLARE @.st VARCHAR(50)
SET @.st=''
SELECT @.st=@.st+ col2+','+col3 FROM #Test WHERE col1=1
SELECT @.st
"JS" <JS@.discussions.microsoft.com> wrote in message
news:A122B6DE-DD0D-49D6-99A1-A5D7F63933F0@.microsoft.com...
> Hi,
> I am a new comer to sql server. I could write simple procedures. I
> have
> a table like this
> Field1 Field2 Field3
> 1 one name-1
> 2 two name-2
> 1 one name-3
>
> I have to write a procedure to return the values of the records for all
> the
> records with Field1 equal to 1 in the format:
> 1,one,name-1,name-3
>
> As in the above case two records match so I return total four values with
> two name values. If three records would have matched I would have returned
> three name values in the response, so in that case the total values to be
> returned would have been 5.
> I just wonder how I would write this procedure some thing like
> create procedure dbo.getvalues input int, output1 int OUTPUT, output2
> char(10) OUTPUT....
> as
> select Field1,Field2,Field3 ...
> How can I declare a variable limit to output fileld ? I mean the output
> from
> this procedure could be 4 fields 5 fields or 6 fileds or more ....
> Thanks for any help in this matter.
> JS
>|||Maybe this might help:
http://milambda.blogspot.com/2005/0...s-as-array.html
And I strongly agree with Uri - this belongs on the presentation layer, not
the data layer.
MLsql

How to let general user use xp_cmdshell?

Hi,

Is any way can let user (not admin role) use xp_cmdshell?

I write a SP and include xp_cmdshell to execute .exe file.

So I hope user can execute xp_cmdshell, too!

Any idea?

Thanks!

First, why would you want a user running xp_cmdshell? Talk about an open entry to your servers. Does your system admin know about this?

Second, not 100% but I think SP2 eliminated the ability for processes to run xp_cmdshell.

how to know status of previous control flow

Hi,

I have a package which has 3 data flows and a script component to write log file. Those 3 data flows are in sequence (mean upon success, next will run). If the first 2 data flows fails, control will go to script and upon completion of 3rd, control will go to script to create log file.

Now, my question is, within my script component, is there any way to find out what was the last ran data flow and its status? What I know is, adding a 'activex task' between each data flow and script component which updates a variable and then I can use that variable in the script component to know the last ran data flow. But what I want to know is, is there any system variable (or something) which will do the samething without any need to add additional task?

Thx.

Where you say script component I assume you mean script task? (the two are different you see)

There is no way of knowing what the previous task is unless you tell it. You are proposing to do this with an ActiveX Script task. I recommend you don't do that as the are only there for backward compatibility. Use a script task instead.

-Jamie

|||

If you mean a script task, you can do this as follows:

Create a string variable at the scope of the package - call it something like LastTask. http://msdn2.microsoft.com/en-us/library/ms141670(SQL.90).aspx

Create a sequence container and build your control flow inside the sequence - you can drag in existing objects, or copy and paste them in. http://msdn2.microsoft.com/en-us/library/ms139855.aspx

Create an OnPostExecute EventHandler for the sequence container. http://msdn2.microsoft.com/en-us/library/ms139744(SQL.90).aspx

Add a Script Task to the EventHandler. Configure the Script Task to use your LastTask variable as a ReadWrite variable and the system variable SourceName. (For detailed info on using variables with the script task see http://msdn2.microsoft.com/en-us/library/ms135941.aspx)

In the ScriptTask you could use the following line to set LastTask to the name of the last task to execute within the sequence ...

Dts.Variables("LastTask").Value = Dts.Variables("SourceName").Value

If you need to insert this value into a data flow, you could use the DerivedColumn component to insert the value of LastTask into a new column in each row passing.

hth

Donald

|||

Hi Donald,

Thx for giving me the perfect solution. I have couple of questions to that. The above method will tell me which is the last task. But how do I know the status of the task? Also in case of failure how do I know the error message?

Thx again.

how to know database space on SQL 6.5 by command line

Hello,
i'll write a scripte to check my databases.
My need know the space use by the database & device,
the same for the space free.
Can you help me..
ThanksHi
I don't have 6.5 access at the moment, but you may want to check out if
sp_helpdb <dbname> run from isql, gives you something you can work with.
John
"bfav" <bfav@.ch.ibm.com> wrote in message
news:087701c3506d$49c01bd0$a001280a@.phx.gbl...
> Hello,
> i'll write a scripte to check my databases.
> My need know the space use by the database & device,
> the same for the space free.
> Can you help me..
> Thanks