how to construct dynamic sql in stored procedure

Hi Guys.

I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this

CREATE PROCEDURE P_SAMPLE

(

@.P_ONE NVARCHAR(240)

)

AS

BEGIN

DECLARE

@.TMP_CNT INT

BEGIN

EXEC ('SELECT @.TMP_CNT = 1')

-- PRINT @.TMP_CNT

END

END

Regards

Mani

Use sp_executesql to pass/retrive the data to/from dynamic sql. Exec won’t help you here.

Code Snippet

DECLARE @.TMP_CNT INT

DECLARE @.SQL as NVarchar(4000)

DECLARE @.Params as NVarchar(4000)

Set @.SQL = N'SELECT @.TMP_CNT = 1'

Set @.Params = N'@.TMP_CNT as Int OUTPUT'

Exec sp_executesql @.SQL, @.Params, @.TMP_CNT OUTPUT

Select @.TMP_CNT

|||

here is one of the best resource in Dynamic sql

http://www.sommarskog.se/dynamic_sql.html

Madhu

How to Concatenate Strings That Have Trailing Spaces?

I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely. This means
I need to append trailing spaces to a text string (such as "Test1 ")
or append leading space in front of a text string that contains a
number (such as " 12.00"). Now, the stored procedure works fine when
I run it in Query Analyzer. But it doesn't work correctly when I run
it using ISQL - All the columns are not indented. I am wondering why
it doesn't work in ISQL.

This is what I want, and this is also what I get when I run the stored
procedure using Query Analyzer:

Test1 , 2,Test1.txt , 1.00, 1.00
Test22 , 2,Test22.txt , ,
Test333 , 2,Test333.txt , 30.00, 30.00

This is what I get if I run the stored procedure using ISQL
(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):

Test1, 2,Test1.txt, 1.00, 1.00
Test22, 2,Test22.txt, ,
Test333, 2,Test333.txt, 30.00, 30.00

You can see that the result from ISQL has the following differences:
1. It puts a space in front of each row.
2. It appends enough spaces at the end of each line to make
the line length to be exactly 61 characters.
3. It gets rid of the trailing space from each column.
4. It leaves only one blank space if the column has nothing
but a serie of spaces.

The following is the stored procedure that I am testing:

create procedure MyTest
as

set nocount on

create table #Test
(
Field1 varchar(10) null,
Field2 varchar( 5) null,
Field3 varchar(20) null,
Field4 varchar(10) null,
Field5 varchar(10) null
)
insert into #Test values
( "Test1 ", " 2","Test1.txt ", " 1.00", " 1.00" )
insert into #Test values
( "Test22 ", " 2","Test22.txt ", " ", " " )
insert into #Test values
( "Test333 ", " 2","Test333.txt ", " 30.00", " 30.00" )

select Field1 + "," +
Field2 + "," +
Field3 + "," +
Field4 + "," +
Field5
from #Test

drop table #Test
go

Strangely, the differences #3 and #4 only show up when I use the
SELECT statement on a table. They don't show up when I use SELECT
statements to show constant text strings or string variables, like
this:

set nocount on
select "Test1 " + "," +
" 2" + "," +
"Test1.txt " + "," +
" 1.00" + "," +
" 1.00"
select "Test22 " + "," +
" 2" + "," +
"Test22.txt " + "," +
" " + "," +
" "
select "Test333 " + "," +
" 2" + "," +
"Test333.txt " + "," +
" 30.00" + "," +
" 30.00"

The result is like the following if I use constant text strings or
string variables:

Test1 , 2,Test1.txt , 1.00, 1.00

Test22 , 2,Test22.txt , ,

Test333 , 2,Test333.txt , 30.00, 30.00

I need to run it from ISQL because that is how I run _all_ my other
stored procedures. I don't want to do anything differently just
because I need to run this stored procedure.

Thanks in advance for any suggestion.

Jay Chan"Jay Chan" <jaykchan@.hotmail.com> wrote in message
news:c7e5acb2.0308120646.2c8593ec@.posting.google.c om...
> I am trying to export data from a SQLServer database into a text file
> using a stored procedure. I want to be able to read it and debug it
> easily; therefore, I want all the columns to indent nicely. This means
> I need to append trailing spaces to a text string (such as "Test1 ")
> or append leading space in front of a text string that contains a
> number (such as " 12.00"). Now, the stored procedure works fine when
> I run it in Query Analyzer. But it doesn't work correctly when I run
> it using ISQL - All the columns are not indented. I am wondering why
> it doesn't work in ISQL.

Check out the 'SET ANSI_PADDING' setting in BOL.

Ian.|||Jay Chan (jaykchan@.hotmail.com) writes:
> I am trying to export data from a SQLServer database into a text file
> using a stored procedure. I want to be able to read it and debug it
> easily; therefore, I want all the columns to indent nicely. This means
> I need to append trailing spaces to a text string (such as "Test1 ")
> or append leading space in front of a text string that contains a
> number (such as " 12.00"). Now, the stored procedure works fine when
> I run it in Query Analyzer. But it doesn't work correctly when I run
> it using ISQL - All the columns are not indented. I am wondering why
> it doesn't work in ISQL.

This is because with ISQL you get a different setting for ANSI_PADDING.
This setting is OFF by default with ISQL, but ON by default with Query
Analyzer. The effect of this setting is that if it is OFF, SQL Server
trims trailing spaces from varchar data when you insert it.

Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.

The setting is actually saved with the table column, so if you create
the table in QA, it should work in ISQL even with the setting off. (But
I have not tested this.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Check out the 'SET ANSI_PADDING' setting in BOL.

Thanks! This switch fixes the problem very nicely. Now, all the
columns indent correctly.

Jay Chan|||> This is because with ISQL you get a different setting for ANSI_PADDING.
> This setting is OFF by default with ISQL, but ON by default with Query
> Analyzer. The effect of this setting is that if it is OFF, SQL Server
> trims trailing spaces from varchar data when you insert it.
> Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.

Yes, you are right. I add the switch in my stored procedure right
before it starts creating temporary tables, and now it can indent the
column of info quite nicely. Thanks.

> The setting is actually saved with the table column, so if you create
> the table in QA, it should work in ISQL even with the setting off. (But
> I have not tested this.)

I believe this is correct according to the Help info on that switch. I
also create tables in Query Analyzer; this explains why I haven't come
across this problem until now.

Jay Chan

How to compare table from SQLSERVER 2005 with oracle

Hi,

i am trying to compare the table which is existing in SQL server2005 with the same in oracle 10g. is there any short cut procedure for it..?

Sreenivas

What do you want to compare the schema of the data ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||i want to compare the table structure including the datatypes of some xxx table in sql server with oracle table which is having the same name and structure..these two are located in different databases and different locations with different schema names..|||

I have a script for SQL Server which does the things on two different SQL Server databases. For Oracle you would have to do a comparison betwen the datatype which can differ although they are in common the same. Just write me a mail if you want to have this script.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jen,

Could u mail me the script for SQL server comparison..i dont have any script for oracle ...

I am trying to reach you at your mail id. but surprisely it bounce back to me..

How to commit a transaction even when trigger fails

I have a row that, when updated, triggers a stored procedure. However, if this stored procedure fails, the row is never updated at all.

I know with SQL 2000, you can specify on on_event argument of AFTER so that the transaction is actually "committed" before the trigger goes off, but is there a similar way to do this with SQL 7? I can't seem to find a way to have the "trigger" action take effect regardless if whatever action the trigger causes fails or not.

EdCould you issue a commit right before calling the sp?

How to combine two columns into one in an sql

I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.

Here is the stored Proc:

SELECTDATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME),COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

It returns a table:

MONTH DATE TOTAL NUMBER OF CALLS
======= ===== ===========
1 1 10

1 2 15

My question is: is it possible to combine the Month and Date column into one column. e.g.

Date Total Number of Calls
==== ==============
1/1 10
1/2 15

Please Help, Thanks in advance :)

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME)) AS [Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME)) + '/' + CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

|||

thx, I did some configuration. works like a charm

SELECT CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))AS[Date],COUNT(*)

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(PINCODEIS NOT NULL)

GROUP BY DATEPART(mm, CALLSTARTTIME) ,DatePart(dd, Callstarttime),CONVERT(nvarchar(2),DATEPART(mm, CALLSTARTTIME)) + '/' +CONVERT(nvarchar(2),DATEPART(dd, CALLSTARTTIME))

ORDER BY DATEPART(mm, CALLSTARTTIME),DATEPART(dd, CALLSTARTTIME)

How to combine MAx statement with specific search?

Hello everyone!

I've got a table (of "Persons") with 2 columns: "Age" and "Name".
I make a procedure that get the MAX of "Age" of the table, like this:

SELECT
MAX (p.Age) MaxAge
FROM
Persons p

But I'll like to add a new column to my result table. That column corresponds to the "Age" of a person 'X'. 'X' is its 'Name' and i will pass it by parameter to the procedure.
Something like this:

SELECT

MAX (p.Age) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X

FROM

Persons p

That should returns a single row result table with the max of all 'Age" and the 'Age' of the person 'X'. Anyone can help me to do this?

Thank you to everyon, and a happy new year

Jonathan

You can use a subquery, a derived table or store the result in a variable.

i.e. the sub query one looks like this

select (select max(age) from person) as maxAge, age

from person

where name = @.name

|||

You can use the following query,

SELECT
(Selecct MAX (Age) From Persons) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X
FROM
Persons P

|||Tahnk you very much. But I think I've simplified too much my query problem. I've got a third column. That its' cities. The original table is actually "Age", "Person","City". I need the maximum age and the age of the person X in each city (multiple rows result table). Could you help me?

Thank you evryone, specially:Mani and Simon.|||

In which case a derived table is the best option

select maxAge.age

, person.age

, city

from (select max(age) age, city from person where name = @.name group by city ) maxage

join person on person.city = maxage.city

where name = @.name

How to code ASP.NET page with return value Store Procedure?

Does anyone know how to call a SQL store procedure that return a value to the page?

I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.

Are there a similar article somewhere?

Thank you all!

check out the docs about using ExecuteScalar() Method.|||

Using ExecuteScalar() requires a separate procedure call. I would like to use the same procedure call that to insert a record into a table and also return an ID back. My store procedure look like this:

sqlString = "EXEC spInsertRow 'parm1','parm2','parm3', @.ReturnID OUTPUT"

Thanks

|||

In the end of your sproc:

SELECT @.ReturnID = @.@.IDENTITY

|||

I suggested ExecuteScalar assuming you are doing some insert and want to return the ID.

If that is not the case you can use the OUTPUT parameters..

Dim res as integer

myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.returnId"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)

res = mycommand.Parameters("@.result").Value

|||

Thank you. It works

|||

Can you share your general procedure. I'm having the same issue and have tested many ways. Still not working.

See posthttp://forums.asp.net/1178243/ShowPost.aspx

Thanks

How to code an aspx page to run a stored procedure with a parameter

My stored proceddure "My Programs" includes a parameter @.meid.
How do I code an aspx file to run the procedure with a user ID, e.g. EmpID?
I tried the following codes, but the error message indicated it can not find
the Stored Procedure. How do I pass the EmpID as a Stored Procedure parameter?

<%
meid = EmpId
cmd.CommandText ="MyPrograms meid"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader(meid)
While reader3.Read()
sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")
EndWhile
%>

TIA,
Jeffrey

Check if this helps:http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx

|||

Thanks.

My co-worker told me I am supposed not need to know the parameter name, "@.meid".
Then what does "Failed to convert parameter value from a String to a Int32" mean?
Why the ExecuteReader() needs to convert EmpID to Int32? It's laready an integer.

TIA,
Jeffrey

<%
cmd.CommandText ="MyPrograms"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@.meid", SqlDbType.Int))
cmd.Parameters("@.meid").Value =EmpID
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader() Failed to convert parameter value from a String to a Int32.]

While reader3.Read()

sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")

EndWhile

|||

I think I have solved the problem. The reason for the rror: "Failed to convert parameter value from a String to a Int32"
is Value = "EmpID". It should be EmpID.

Thanks gain.

How to check which tables were called?

Hello everyone:
I have some nightly jobs that execute stored procedure to call the tables? I want to know which table are called by these stored procedures. Is it possible? Any idea will be appreciated.
Thanks
ZYTLemme get this right, you have a series of stored procedures that run every night, but want a list of the affected tables? basically a listing of the tables in those stored procedures run at night?

Basically, in stead of reading through the SP's and noting the tables used, you want an automation process?|||Yes, I need a table list that are affected in the nightly job/|||Just do sp_depends on all of the stored procedures

USE Northwind
GO

CREATE PROC mySproc99
AS
SELECT * FROM Orders
GO

EXEC sp_depends mySproc99
GO

DROP PROC mySproc99
GO|||...although be aware that this will not show tables referenced in dynamic SQL statements embedded in your procedures.|||True..and if the tables was dropped and recreated it won't show as well..

How about adding some triggers