Hello,
The SqlString variable; if a null value is assigned, will Value return the text null? Also, if I use another type such as SqlInt32, when I do ToString(), if the value is null, will it return the text null, so that it will work with a SQL string? I was wondering if that was the case.
Brian
I want to pass a database parm to enlarge the maxsize for around 500 databases. Here is the primary script:
--====================================
declare @stringData varchar(200),
@stringLog varchar(200),
@databaseName varchar(25),
@returnCodeSize int
select @databaseName = 'ABC'
select @stringData = 'alter database ' + @databaseName +
' modify file (name = ABC_Data, maxsize = 1500MB, FILEGROWTH = 10%)'
print @stringData
exec (@stringData)
It works in above way.
But it did not work if
I use @returnCodeSize =exec(@stringData)
how could I get a return code from this exec. I can't use cmdshell since it is not an external operating command.
thanks
David
Hi,
I am getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
This is my code. What is wrong here?
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))
--Inbound
SET @LadingType = 'inbound'
SET @SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @ReportData + '.InvoiceNum, ' +
@ReportData + '.ShipperNum, ' +
@ReportData + '.InvoiceDate, ' +
@InvoiceData + '.PickupTransDate, ' +
@AddrData + '.aName1, ' +
@AddrData + '.aName2, ' +
@AddrData + '.aAddr1, ' +
@AddrData + '.aCity, ' +
@AddrData + '.aState, ' +
@AddrData + '.aZip, ' +
@ReportData + '.bName1, ' +
@AddrData + '.bName2, ' +
@AddrData + '.bAddr1, ' +
@ReportData + '.bCity, ' +
@ReportData + '.bState, ' +
@AddrData + '.bZip AS, ' +
@AddrData + '.bCountry, ' +
@AddrData + '.bPhone, ' +
@ReportData + '.TrackingNum, ' +
@InvoiceData + '.CustRef1, ' +
@InvoiceData + '.CustRef2, ' +
@ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@InvoiceData + '.GrossCharge, ' +
@ReportData + '.Incentive, ' +
@ReportData + '.NetCharge, ' +
@ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@InvoiceData + '.HundredWeight ' +
'FROM' + @ReportData +
' INNER JOIN ' + @InvoiceData + ' ON ' + @ReportData + '.DataID = ' + @InvoiceData + '.DataID ' +
'INNER JOIN ' + @AddrData + ' ON ' + @ReportData + '.DataID = ' + @AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE(' + @ReportData + '.InvoiceDate BETWEEN ''' + CAST(@startdate AS varchar) + ''' AND ''' + CAST(@enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @LadingType + ''')'
EXEC (@SQLStr)
Hello, I have been working around this issue, but couldn't yet find any solution.I have a stored procedure that calls a method to do a certain repetitive work.In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.for example, there is an input for a function called "Id"Inside the function, if Id = 111I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.Now, inside the function, I need to return a value by executing the above @SQLString as follows:EXEC @SQLStringWhen I need is something likeEXEC @SQLString, @Total OutputReturn (@Total)Are there any ideas ?regards
View 1 Replies View RelatedAnyone know how to return a sql command from a sql server stored procedure using asp.net c# and sql server 2000?I'm trying to debug the stored proc and thought the easiest way would be to return the insert command and debug it in the query analyzer.Thanks.Doug.
View 2 Replies View RelatedHi,
How can I check return value from
EXECUTE sp_executesql(SQLString)
Thanks,
How do we do this in SS2k5?
in
EXEC(sqlstring)
sqlstring wants to pass back a resultset to the caller.
- Local temp tables are out of scope.
- Global temp table works but is a bad idea.
- Table variables not supported as OUTPUT parameters for EXEC.
Regards, Nick
Hi All,
Create proc sproc_Insert
@TableName varchar(50),
@InsertColumns varchar(1000),
@InsertValues varchar(2000),
@WhereCondition varchar(200)
as
Begin
Declare @CheckStr nVarchar(2000)
Declare @RetVal int
Set @checkStr = 'Select * from '+ @TableName + ' '+ @WhereCondition
execute sp_executesql @checkStr,@RetVal output
print @RetVal
End
I am not able to retrieve the return value in the above procedure. For example if data exists then 1 else o
Thanks & Regards
Bijay